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
Else
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: " & Mid(c.Formula, i, 0) & " = " & Asc(Mid(c.Formula, i, 1))
allNumbers = False
Exit For
Else
' MsgBox Mid(c.Formula, i, 1) & " = " & Asc(Mid(c.Formula, i, 1))
End If
Next
If allNumbers Then
c.Font.ColorIndex = 5 ' blue
Else
c.Font.ColorIndex = 0 ' auto
End If
End If
Else
c.Font.ColorIndex = 5
End If
Next
End Sub
```

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