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,660 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 113K 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 No comments

  2. 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 No comments