Excel VBA - Financial Model Color Formatting

Question

In financial models it is common to colour-code the cells based on their input (see here for example). I would like to create a macro that automates this task for me.

The required colour-code is the following

• Blue: Constants (except text)
• Black: Formulas
• Green: References to other sheets
• Red: References to separate files or external links

Thanks to the great answers of Rory and Samuel I was able to achieve the above with the following code:

Sub financial_color_coding()

' Color hard-coded cells blue
With Selection.SpecialCells(xlCellTypeConstants, 21).Font
        .Color = -65536 ' colour selected cells blue
        .TintAndShade = 0
End With

' Select cells that contain formulas
Selection.SpecialCells(xlCellTypeFormulas, 23).Select

'Color selected cells based on their input
For Each cell In Selection
   If Left(cell.Formula & " ", 1) = "=" Then
      If InStr(CleanStr(cell.Formula), "]") Then
         cell.Font.Color = RGB(255, 0, 0) ' red for references to other files
       ElseIf InStr(CleanStr(cell.Formula), "!") Then
            cell.Font.Color = RGB(0, 150, 0)     ' green for references to other sheets
       Else
            cell.Font.Color = RGB(0, 0, 0) 'black for every other formula
        End If
    End If
Next cell

End Sub

Function CleanStr(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
   .Pattern = "\""[^)]*\"""
   .Global = True
   CleanStr = .Replace(strIn, vbNullString)
End With
End Function

Running the marco will only change the font of cells in a workbook that contain either a constant number or a formula and will keep the overall formatting of the text unchanged.

Answer 1

SpecialCells are documented here:

https://learn.microsoft.com/en-us/office/vba/api/excel.range.specialcells

However, not all can be done with it. If a formula contains a ! or ] it references to another sheet or file. The CleanStr removes all text in quotes, since these text may also include these characters.

Selection.SpecialCells(xlCellTypeConstants).Font.Color = RGB(0, 0, 255) 'blue for constant
Selection.SpecialCells(xlCellTypeFormulas).Font.Color = RGB(0, 0, 0) 'black for formulas

'to be more specifiy
For Each cell In Selection
    If Left(cell.Formula & " ", 1) = "=" Then
        If InStr(CleanStr(cell.Formula), "]") Then
            cell.Font.Color = RGB(255, 0, 0) ' red for references to other files
        ElseIf InStr(CleanStr(cell.Formula), "!") Then
            cell.Font.Color = RGB(0, 150, 0)     ' green for references to other sheets
        Else
            cell.Font.Color = RGB(250, 0, 255) 'pink for formulars with output text
        End If
    ElseIf Not IsNumeric(cell.Text) Then
        cell.Font.Color = RGB(0, 0, 0) 'black for text constant
    End If
Next cell

The CleanStr is adopted from here: Remove text that is between two specific characters of a string

Function CleanStr(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
   .Pattern = "\""[^)]*\"""
   .Global = True
   CleanStr = .Replace(strIn, vbNullString)
End With
End Function