VBA, search string within a formula in excel

0
votes

Is it possible to search specific text in a formula using vba? For example: I have a bunch of index(array match(array,"criteria"0),0) and other sum formulas. I want to search formulas with "index" and change cells color.

Thus far I have written the following code:

For Each cell In ActiveSheet.UsedRange 'color cells having formulas
    If cell.HasFormula Then 
        Find = "*index*" 
        cell.Font.Color = indexcolor
    End if
Next cell
2
stringexcelvbasearchtext
Yes, it's possible. - findwindow
Please see how to ask. - findwindow
You can use the Range.Find method to look through formulas and then you can use c.Interior.Color = vbRed to highlight the cells. The above link even includes the VBA code you need (pre-written by Microsoft for you). - Ralph

2 Answers

0
votes

How about:

Sub LookinForDory()
    Dim r As Range, s As String
    For Each r In ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
        s = LCase(r.Formula)
        If InStr(1, s, "index") > 0 Then
            r.Interior.ColorIndex = 27
        End If
    Next r
End Sub
0
votes
Sub Changecellcolor()

Dim formulaColor As Long
Dim cell As Range

LinkedCells = RGB(Red:=0, Green:=0, Blue:=255)

    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If InStr(1, cell.Formula, "index") > 0 Then
    cell.Font.Color = LinkedCells
    End If

End Sub