Tap
Alt+F11 and in the same module sheet, paste the following
User Defined Function (i.e.
UDF) code,
Public Function fcnFinancial_Color_Codes(ByRef rng As Range) As Integer
Dim rErr As Range
fcnFinancial_Color_Codes = 0
On Error Resume Next
If rng.HasFormula Then
Set rErr = Range(Mid(rng.Formula, 2, Len(rng.Formula) - 1))
If CBool(Err) Then
fcnFinancial_Color_Codes = 1 'formula
Else
fcnFinancial_Color_Codes = 2 'reference
End If
Err = 0
ElseIf CBool(Len(rng.Value)) Then
fcnFinancial_Color_Codes = 4 'value
End If
Set rErr = Nothing
End Function
Tap Alt+Q to return to your worksheet. You can now use this UDF in conditional formatting. Select a range (e.g. D2:D99), noting the
active cell (e.g. D2) and create a new Conditional Formatting Rule based upon a formula and use the following for
Format values where this formula is true:,
=fcnFinancial_Color_Codes(D2)=4
Click Format and set a yellow
Fill and a blue font. Click
OK to accept the formatting and then
OK to create the new rule. That should set the typed values as blue text with a yellow fill. Repeat for two more new CF rules with =fcnFinancial_Color_Codes(D2)=1
and =fcnFinancial_Color_Codes(D2)=2, setting the CF format as appropriate for each.
