[VBA] For next require vs for each

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
Code:
Sub D_W()
      Dim X As Range, Y As Range
            For Each X In Range("B3:g3")
                  Set Y = Range("J5:Q10").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                           If Not Y Is Nothing Then
                                     Y.Interior.ColorIndex = 6
                           End If
            Next X
End Sub
Hello, Hope a Great week for all.
Well, I am trying to replace this ►”FOR/EACH”◄ method, because I don’t see the possible way to control the ranges B:G and J:Q.

What I mean by this?
If I have for next statement I will be able to create two variables like [ X, Y] and just make:
x = Range(“A1”).value, and y = Range(“B1”).value,
so then my loop will be :

For m = x to y etc.

This is the kind of control I need in both ranges of the actual loop, but I don’t see how to do it with for/each statement.
“Any help is always appreciated”.
Thank you for reading this post.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,037
Office Version
2016
Platform
Windows
Code:
Sub D_W()
Dim X As Range
Range("J5:Q10").Interior.Color = xlNone
For Each X In Range("B3:G3")
    Application.FindFormat.Clear
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   Range("J5:Q10").Replace What:=X, Replacement:=X, LookAt:=xlWhole, ReplaceFormat:=True
Next X
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,288
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top