Excel colour coding macro for formulas, constants

Question

Hello,

Can you help me create a macro that will color-code cells in excel (font colour) according to the following color-scheme (normally useful in financial models)?

Blue - Hard-coded values (numbers)

Black - Formulas

Red - Formulas with constant in them (for e.g. =sum(a2:a5)+1, a2*3.. etc) 

Green - References from another worksheet  / workbook

Ideally I would like to use a short cut key to run the macro for the entire workbook. I am not very familiar with macros or visual basic.

Thanks much for the help.

Karthik

Answer 1

Perhaps this way if I have not made some mistake:

Sub ColorDifCells()

Signs = Array("=", "-", "+", "*", "/", "*")

For Each Cell In ActiveSheet.UsedRange

  CF = Cell.Formula

  Cell.Font.Color = vbBlack

  If Not Cell.HasFormula Then

    Cell.Font.Color = vbBlue

  ElseIf InStr(1, CF, "!") Then

    Cell.Font.Color = vbGreen

  Else:

    For I = 2 To Len(CF)

      For J = LBound(Signs) To UBound(Signs)

      If IsNumeric(Mid$(CF, I, 1)) And Mid$(CF, I - 1, 1) = Signs(J) Then _

        Cell.Font.Color = vbRed: GoTo NextCell

      Next J

    Next I

  End If

NextCell:

Next

End Sub

'Blue - Hard-coded values (numbers)

'Black -Formulas

'Red - Formulas with constant in them (for e.g. =sum(a2:a5)+1, a2*3.. etc)

'Green - References from another worksheet  / workbook

Regards

PB

Answer 2

Thanks a ton!! This worked very well.

Answer 3

Though the mistake, I had been afraid of, have crept into. The last '*' should be '('  -  for the case:   = (3+a2).

Anyway I am glad even this served well.

PB