Excel VBA

Change Font Color in Cells with VBA

User Rating:  / 4
PoorBest 
Details
 
VBA Code for customized Font Color in Cells
Code for different font color in cells having "formulas", "formulas and numbers" and "constants".
 

 
Sub FontColorFormulas1()
'Worksheet Code for different font color in cells having formulas, formulas and numbers and constants. Run this code to change font colors in Used Range in Active Sheet.
Dim formulaColor As Long
Dim formulanumbersColor As Long
Dim constantColor As Long
Dim cell As Range

formulaColor = RGB(Red:=0, Green:=255, Blue:=0)
formulanumbersColor = RGB(Red:=0, Green:=0, Blue:=0)
constantColor = RGB(Red:=0, Green:=0, Blue:=255)

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)'color cells having formulas
cell.Font.Color = formulaColor
Next cell
 
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)'color cells having formulas and having nos.cell.Font.Color = formulanumbersColor
Next cell

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)'color cells having constants (non-formulas)
cell.Font.Color = constantColor
Next cell

End Sub
 

 
Sub FontColorFormulas2()
'Worksheet Code for different font color in cells having formulas, formulas and numbers and constants. Run this code to change font colors in Used Range in Active Sheet.

Dim formulaColor As Long
Dim formulanumbersColor As Long
Dim constantColor As Long
Dim cell As Range

formulaColor = RGB(Red:=0, Green:=255, Blue:=0)
formulanumbersColor = RGB(Red:=0, Green:=0, Blue:=0)
constantColor = RGB(Red:=0, Green:=0, Blue:=255)

For Each cell In ActiveSheet.UsedRange

If cell.HasFormula = True Then'color cells having formulas
cell.Font.Color = formulaColor
 
If IsNumeric(cell) = True Then'color cells having formulas and having nos.
cell.Font.Color = formulanumbersColor
End If

Else
cell.Font.Color = constantColor'color cells having constants (non-formulas)
End If

Next cell

End Sub
 

 
Private Sub Worksheet_Change(ByVal Target As Range)
'Worksheet Code for different font color in cells having formulas, formulas and numbers and constants. This code sets a preference in the worksheet for relevant font colors, as and when cell values are entered/changed.

Dim formulaColor As Long
Dim formulanumbersColor As Long
Dim constantColor As Long

formulaColor = RGB(Red:=0, Green:=255, Blue:=0)
formulanumbersColor = RGB(Red:=0, Green:=0, Blue:=0)
constantColor = RGB(Red:=0, Green:=0, Blue:=255)

With Target

If .HasFormula Then'color cells having formulas
.Font.Color = formulaColor

If IsNumeric(Target) Then'color cells having formulas and having nos.
.Font.Color = formulanumbersColor
End If

Else
.Font.Color = constantColor'color cells having constants (non-formulas)
End If

End With

End Sub
 

 
   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.