VBA Code for customized Font Color in CellsCode 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 LongDim formulanumbersColor As LongDim constantColor As LongDim 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 = formulaColorNext cell 'color cells having formulas and having nos. For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)cell.Font.Color = formulanumbersColorNext cell 'color cells having constants (non-formulas) For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)cell.Font.Color = constantColorNext 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 LongDim formulanumbersColor As LongDim constantColor As LongDim 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 Thencell.Font.Color = formulaColor 'color cells having formulas and having nos.If IsNumeric(cell) = True Thencell.Font.Color = formulanumbersColorEnd If Else'color cells having constants (non-formulas)cell.Font.Color = constantColorEnd 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 LongDim formulanumbersColor As LongDim 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 = formulanumbersColorEnd If Else'color cells having constants (non-formulas).Font.Color = constantColorEnd If End With End Sub