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
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 GoTo 0 'Back to normal.
End Sub
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 iSet rng=Sheet1.UsedRange.Cells.Find(55, rng)
rng.Interior.Color=vbRed
On Error GoTo 0 'Back to normal.
While the following file outlines the Excel VBA procedure with an example.
