Conditional Formatting can be used to identify cells that have formulas in them. This can be useful if you have a sheet with data entry cells mixed with computations by helping the user to see where they should be entering data.
I don’t know of any way to test a cell for a formula using built-in functions, so we’ll have to resort to a user-defined function (UDF).
Function CellHasFormula(rTarget As Range) As Boolean
If IsEmpty(rTarget) Then
CellHasFormula = True
Else
CellHasFormula = rTarget.HasFormula
End If
End Function
Why test for empty cells? I like to highlight cells where the user is supposed to enter data, not the other way around. For that reason, I generally couple the UDF with a NOT() function. I dislike negative fucntions, except NOT(), so I don’t write a CellDoesntHaveFormula() function. Just another peek into the depths of my insanity. In this example, I apply the conditional formatting to A2:E7.
Dick,
I can see why you’ve done it but
If IsEmpty(rTarget) Then
CellHasFormula = True
does my simple head in!
Jamie.
I am trying to use the CellHasFormula function in Conditional Formatting via VBA, but the procedure mysteriously quits after inserting the condition.
Strangely, if I do not include an equal sign before the condition formula, everything proceeds and then I have to manually go in and remove quote marks.
Here is the relevant codeblock:
With Cells(EndR, j)
.Select
FormatRange = jStr & StartR + 1 & “:” & jStr & EndR – 1
FormatFormula = “AND(NOT(CellHasFormula(” & Replace(.Address, “$”, “”) & _
“)),COUNT(” & FormatRange & “)=0)”
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:= FormatFormula
The variables used are:
j – column #
jstr – column in letters
StartR – Start of block above cell
EndR – End of block above cell
Notice that I do not currently have an equal sign (=) before the formula. If I put it in, the procedure abruptly quits.
I appreciate any assistance provided…
The relevant line should read:
.FormatConditions.Add Type:=xlExpression, Formula1:=FormatFormula
I copied the old commented line…
“does my simple head in!”
I know just what you mean. Every time I uses I tell myself it’s time to think of a better way, but I never do.
David: I get the same result. I know that CF has problems with UDF’s in other workbooks, but I’ve never seen this before. If you replace the UDF with a built-in function, it works as expected. This is a bug, clearly. Here’s a cruddy workaround if you want. Put the UDF in an unused cell and refer to that in CF formula (I simplified some things for testing purposes).
Sub test()
Dim FormatRange As String
Dim FormatFormula As String
With Range(“A11?)
.Select
On Error Resume Next
.FormatConditions(1).Delete
On Error GoTo 0
Range(“f1?).Formula = “=CellHasFormula(” & .Cells(1, 1).Address & “)”
FormatRange = “A1:A10?
FormatFormula = “=AND(NOT(F1),COUNT(” & FormatRange & “)=0)”
.FormatConditions.Add Type:=xlExpression, Formula1:=FormatFormula
.FormatConditions(1).Font.ColorIndex = 6
End With
End Sub
Well, this is weird… but found another workaround, tested in XP only though.
The trick is clearing the cell before adding the Conditional Format:
Sub Test2()
Dim FormatRange As String
Dim FormatFormula As String
Dim jStr As String, StartR As Long, EndR As Long, j As Long
Dim OldValue As Variant
jStr = “A”
StartR = 1
EndR = 11
j = 1
With Cells(EndR, j)
‘CF bug
OldValue = .Value
.ClearContents
.Select
On Error Resume Next
.FormatConditions(1).Delete
On Error GoTo 0
FormatRange = jStr & StartR + 1 & “:” & jStr & EndR – 1
FormatFormula = “=AND(NOT(CellHasFormula(” & .Address(0, 0) & “)),COUNT(” & FormatRange & “)=0)”
.FormatConditions.Add Type:=xlExpression, Formula1:=FormatFormula
.FormatConditions(1).Font.ColorIndex = 6
‘Restore old value
.Value = OldValue
End With
End Sub
Good one, Juan. That’s way better than using a helper cell.
Why not avoid the UDF altogether and use David Hager’s version (via J-Walk’s page)
It is old xlm code, but I’m guessing it’ll be around forever. Maybe someone at the MVP summit would know otherwise??? It also does not count blanks as formulas as yours does, though I wonder if another GET.CELL function may reproduce that too.
I tried rzf’s method and it worked perfectly!
Thanks to all for your assistance.
Note that David Hager’s version is dangerous when used with XL97 or any Mac version. Copying a cell formula that references an XL4M command and pasting into another sheet will cause XL to crash hard.
How do you conditional format range of cells such that if they have “True” you colour them green and “false” you colour them blue?
I am really new to VBA and would appreciate any help. Where do you insert this in the code?
Thanks
sjt: Go to Conditional Format from the Format menu. Make your first condition
Cell Value Is – equal to – TRUE
and set the format color to green. Make the second condition
Cell Value Is – equal to – FALSE
and set the format color to blue. See also http://www.dicks-blog.com/excel/2004/04/conditional_for.html