Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
detecting formula containing a constant
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
detecting formula containing a constant
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. -- Gary''s Student gsnu200707 "Utkarsh" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula vs constant | Excel Discussion (Misc queries) | |||
Formula for Detecting Currency Format | Excel Worksheet Functions | |||
Formula for detecting a certain pattern in a particular row or column? | Excel Programming | |||
Detecting changes in results of formula | Excel Programming | |||
Create formula for detecting duplicates | Excel Worksheet Functions |