Excel VBA Find All Values in a Range

This Excel VBA procedure will enable you to locate all of values within an Excel range with let you specify a value and highlight each of the specific values. This VBA coding can help you isolate cells which you deem important so you can see patterns in your specific Excel worksheet.

In the following Excel VBA snippet we will isolate the value 55 in the used range of an Excel file. Firstly we find any instance of the cells containing the value 55 then I will loop through each instance and colour the cell red.

The following is the Excel VBA code which will highlight the cells as shown above.

Option Explicit

Sub FindItAll() 'Excel VBA code to highlight all vaules=55
Dim i As Long
Dim rng As Range

Set rng=Sheet1.UsedRange.Cells.Cells(1, 1)

For i=1 To WorksheetFunction.CountIf(Sheet1.UsedRange.Cells, 55)
On Error Resume Next 'Handle no find
Set rng=Sheet1.UsedRange.Cells.Find(55, rng)
rng.Interior.Color=vbRed
Next i
On Error GoTo 0 'Back to normal.
End Sub

While the following file outlines the Excel VBA procedure with an example.