12 thoughts on “Hightlight Cells With Formulas”

1. Dick,
   I can see why you’ve done it but

   If IsEmpty(rTarget) Then
   CellHasFormula = True

   does my simple head in!

   Jamie.

   –

2. 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…

3. The relevant line should read:

   .FormatConditions.Add Type:=xlExpression, Formula1:=FormatFormula

   I copied the old commented line…

4. “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.

5. 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

6. 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

7. Good one, Juan. That’s way better than using a helper cell.

8. 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.

9. I tried rzf’s method and it worked perfectly!

   Thanks to all for your assistance.

10. 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.

11. 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

12. 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

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.