detecting formula containing a constant

[Utkarsh]

Hi

Is there a way to detect formula on a sheet that contain a constant?
For example if a sheet contains formulas like

=A1*10 or =A1+10

Then I would like to change the font color of those cells to red to
flag them.

Utkarsh

 

[Guest]

Try this out:

Sub markum()
' gsnu
Set r = ActiveSheet.UsedRange
Set rf = r.SpecialCells(xlFormulas)
c = Chr(10)
s = Array("=", "+", "-", "*", "/", "^")

For Each rr In rf
v = rr.Formula
For i = 0 To 5
v = Replace(v, s(i), c)
Next
v = Replace(v, "(", "")
v = Replace(v, ")", "")
frags = Split(v, c)
For i = LBound(frags) To UBound(frags)
If IsNumeric(frags(i)) Then
rr.Font.ColorIndex = 3
End If
Next
Next
End Sub


Basically we take each equation and break it into fragments. Then test each
fragment to see if it is a number.

Let me know if you have a formula that doesn't match this code.