Microsoft Office Forums - View Single Post

excelledsoftware · 09-06-2014, 11:12 PM

I have never had much luck with the find all since usually I am looking in thousands of cells of data. However the code below will highlight any cells on the worksheet orange if there is an exact match and the yellow if there is a partial match. Be sure to save and backup your workbook before running.

Code:

Sub HighlightSearch()

  Dim rng As Range, c As Variant, SearchString As String

    SearchString = InputBox("Search the used range for?")
    If SearchString = "" Then End
    Set rng = ActiveSheet.UsedRange
  
    For Each c In rng
      If InStr(1, UCase(c.Value), UCase(SearchString)) Then
        c.Interior.ColorIndex = 6 'highlights yellow
      End If
      If UCase(c.Value) = UCase(SearchString) Then
        c.Interior.ColorIndex = 45 'highlights orange
      End If
    Next c

End Sub