Results 1 to 3 of 3

Thread: [VBA] For next require vs for each

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] For next require vs for each

    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.

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: [VBA] For next require vs for each

    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 by footoo; Jul 22nd, 2019 at 12:00 AM.

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] For next require vs for each

    footoo, Thank you Sir for your Answer.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •