Excel VBA

Change Font Color in Cells with VBA

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
 
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)
 
'color cells having formulas 
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
cell.Font.Color = formulaColor
Next cell
 
'color cells having formulas and having nos. 
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
cell.Font.Color = formulanumbersColor
Next cell
 
'color cells having constants (non-formulas) 
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
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
'color cells having formulas 
If cell.HasFormula = True Then
cell.Font.Color = formulaColor
 
'color cells having formulas and having nos.
If IsNumeric(cell) = True Then
cell.Font.Color = formulanumbersColor
End If
 
Else
'color cells having constants (non-formulas)
cell.Font.Color = constantColor
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
 
'color cells having formulas 
If .HasFormula Then
.Font.Color = formulaColor
 
'color cells having formulas and having nos. 
If IsNumeric(Target) Then
.Font.Color = formulanumbersColor
End If
 
Else
'color cells having constants (non-formulas)
.Font.Color = constantColor
End If
 
End With
 
End Sub
 
 
  • You are here:  
  • Home
  • Excel VBA
  • Change Font Color in Cells with VBA