Problem: I want to hi-light all cells that contain a formula - but by using a macro, so that I can later un-hi-light that same range. In other words, I want to easily see where formulas have replaced hard numbers, and then revert back just as quickly. I realize that there is a "Show Formulas" menu option, but it is messy, and it does not make the relevant cells any more visible.
Currently, I can hi-light the cells by creating the following user-defined function:
Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function
...and then using conditional formatting over a range, using the "formula to determine which cells...." as follows:
=IsFormula(A1) ..... [applied to a range, of course]
This works great. But now I want to have a macro (button) create that condition, with another macro button to delete the condition. Excel will allow a macro to delete conditions just fine. And Excel will allow a macro to create conditions based on cell values, etc. But Excel does not seem to allow a macro to create a condition based on a user-defined function.
If I record a macro of my steps, I get the following:
Sub Hi_Light_Formulas()
Range("A1:Z999").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=isformula(A1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
The problem is that nothing happens. When I check the conditional formatting rules, it says "No Format Set."
Any help would be appreciated.
Bookmarks