Share via

Please help me to shorten this code with loop.

Rajeev Raghavan 1 Reputation point
2021-08-09T08:48:38.683+00:00

valuea = Range("V6")
Last = Cells(Rows.Count, 2).End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, 2).Value) = Range("T6") Then
Cells(i, "A").EntireRow.Resize(valuea, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Activate
Selection.Interior.ColorIndex = 15

valueb = Range("V7")
Last = Cells(Rows.Count, 2).End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, 2).Value) = Range("T7") Then
Cells(i, "A").EntireRow.Resize(valueb, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Activate
Selection.Interior.ColorIndex = 15

valuec = Range("V8")
Last = Cells(Rows.Count, 2).End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, 2).Value) = Range("T8") Then
Cells(i, "A").EntireRow.Resize(valuec, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Activate
Selection.Interior.ColorIndex = 15

121633-vba.jpg

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 114.7K Reputation points
    2021-08-09T20:43:03.52+00:00

    Try something like this: 

    MySub("V6", "T6")
MySub("V7", "T7")
MySub("V8", "T8")

    where MySub is: 

    Sub MySub(ByVal c1 As String, ByVal c2 as String)
    Dim value, Last, i
    value = Range(c1)
    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, 2).Value) = Range(c2) Then
            Cells(i, "A").EntireRow.Resize(value, 11).Select
            Selection.Interior.ColorIndex = 0
        End If
    Next i
    Selection.Activate
    Selection.Interior.ColorIndex = 15
End Sub

    The above series of three calls can be replaced with a loop.

    0 comments
  2. Bob Larson (Access MVP 2008, 2009, 2011) 251 Reputation points
    2021-08-12T18:25:03.937+00:00

    Or just

        Sub grey()

    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1

    If Cells(i, 2).Value = (Range("T6").Value _
    Or Cells(1, 2).Value = Range("T7").Value _
    Or Cells(1, 2).Value = Range("T8").Value) Then

    Cells(i, "A").EntireRow.Resize(Range("V6").Value, 11).Select
    Selection.Interior.ColorIndex = 0

    End If
    Next i

    Selection.Interior.ColorIndex = 15


    End Sub
    0 comments