Help - script loops through a range of cells, compares, and makes a change when match is found

[DonStewart]

I tested this script and it walks through all 27000 cells (D3 - AZ500), changing each that is not blank to a red background:

Dim MyVar As String
Dim i As Integer
Range("D3").Select
For i = 1 To 27000
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Select
Range("D" & (ActiveCell.Row)).Select
Else:
With Selection
.Interior.ColorIndex = 3
ActiveCell.Offset(0, 1).Select
End With
End If
Next

But when I add the code for a "MyVar" variable and change color only when it matches. ONLY the matches in column D are marked. Can anyone see why these extra lines are preventing it from searching, matching, and marking the variable matches in columns E through the first blank cell in that row?

Dim MyVar As String
Dim i As Integer
Range("D3").Select
MyVar = ActiveCell.Value
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Select
Range("D" & (ActiveCell.Row)).Select
Else:
With Selection
If ActiveCell.Value <> MyVar Then
ActiveCell.Offset(0, 1).Select
Else
.Interior.ColorIndex = 3
ActiveCell.Offset(0, 1).Select
End If
End With
End If
Next

 

[Fluff]

Hi & welcome to MrExcel.
Does this do what you want?

Sub ColourMe()
Dim MyVar As String
MyVar = ActiveCell.Value
With Range("D3:AZ500")
   .Replace MyVar, "=XXX", xlWhole, , False, , False, False
   .SpecialCells(xlFormulas, xlErrors).Interior.Color = vbRed
   .Replace "=XXX", MyVar, xlWhole, , False, , False, False
End With
End Sub

 

[DonStewart]

Close. That marks all of the empty cells in red, not the matches. At least it goes through all of the rows AND columns. You've given me some ideas and a new methodology, thank you!

 

[Fluff]

Was the active cell empty when you ran it?

 

[DonStewart]

It was! Good call. After a little work, your method has answered my immediate challenge and we are able to move on. Thank you so very much, well done!

 

[Fluff]

Glad to help & thanks for the feedback