Hightlight Cells With Formulas

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.

HighForm

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

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

    Thanks to all for your assistance.

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

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


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

Leave a Reply

Your email address will not be published. Required fields are marked *