#### Share via

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

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

1. 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.

2. 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
``````