VBA Query for checking Formula

Admin 1
Viewed 220

I am trying to write the VBA Code for checking if the formula exists in a range of cells. Below is my query which is somehow not working(The cells with formulas are not turning red). Can anyone please help me out.

Sub Test()
    Dim LResponse As Integer
    Set rr = Application.InputBox( _
        prompt:="Select a range On this worksheet", _
        Type:=8)
    If rr.HasFormula = TRUE Then
        rr.Interior.Color = vbRed
    End If
End Sub

Edit: I tried looping too

Sub Test()
    Set rr = Application.InputBox( _
        prompt:="Select a range On this worksheet", _
        Type:=8)
    For Each cell In Range(rr)
        If cell.HasFormula = TRUE Then
            cell.Interior.Color = vbRed
        End If
    Next
End Sub
vba excel
1 Answers

From the Range.HasFormula docs:

True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.

Its return value is determined by all the cells having or not having formulas. If only some have formulas, then it is null.

To fix your issue, use a loop over each individual cell:

Dim rng as Range
For Each rng in rr
    If rng.HasFormula Then
        rng.Interior.Color = vbRed
    End If
Next

EDIT: In your loop attempt, drop the Range call:

For Each cell in rr

EDIT 2: You can also use Range.SpecialCells:

On Error Resume Next '<~ an error will occur if there are no formula cells
Dim rng as Range
Set rng = rr.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
    
If Not rng Is Nothing Then
    rng.Interior.Color = vbRed
End If
user_92458530
Related