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 |
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 |
All times are GMT +1. The time now is 08:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com