Auto Color Cells

Many users of Excel have made it common practice to color code cells to help identify inputs, formulas, etc. For example, it is common to color all cells act as hard coded inputs (i.e. not a formula) blue, all formulas black. This Excel macro looks at the contents of each selected cell and sets the color appropriately. Further I have added the green coloring for all external references.

' Set the color of cells to blue or black respectively
' Copyright under GPL by Mark Grimes
' Keyboard Shortcut: Crtl+Shift+C

Sub mgSetColor()
    For Each c In Selection.Cells
        If Left(c.Formula, 1) = "=" Then
            If InStr(c.Formula, ".xls") Or InStr(c.Formula, ".XLS") Then
                c.Font.ColorIndex = 10
            ElseIf InStr(c.Formula, "OFFSET") Then
                c.Font.ColorIndex = 9
                allNumbers = True
                For i = 1 To Len(c.Formula) - 1
                    If (Asc(Mid(c.Formula, i, 1)) < 40) Or (Asc(Mid(c.Formula, i, 1)) > 61) Then
                        ' MsgBox "Setting false: " &amp; Mid(c.Formula, i, 0) &amp; " = " &amp; Asc(Mid(c.Formula, i, 1))
                        allNumbers = False
                        Exit For
                        ' MsgBox Mid(c.Formula, i, 1) &amp; " = " &amp; Asc(Mid(c.Formula, i, 1))
                    End If
                If allNumbers Then
                    c.Font.ColorIndex = 5   ' blue
                    c.Font.ColorIndex = 0   ' auto
                End If
            End If
            c.Font.ColorIndex = 5
        End If
End Sub


July 25, 2005 5:36AM



The contents of this blog are licensed under the Creative Commons “Attribution-Noncommercial-Share Alike 3.0″ license.