how to shorten this code

Question

how to shorten this code

Rajeev Raghavan 1

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 Then

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

End If
Next i

Selection.Interior.ColorIndex = 15

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

If Cells(i, 2).Value = Range("T7").Value Then

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

End If
Next i

Selection.Interior.ColorIndex = 15

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

If Cells(i, 2).Value = Range("T8").Value Then

Cells(i, "A").EntireRow.Resize(Range("V8").Value, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i

Selection.Interior.ColorIndex = 15

End Sub

Sam of Simple Samples 5,586 Reputation points

2021-08-09T21:06:47.86+00:00

I dunno. If you can do all three things in one loop then that would work. Or perhaps you can make a subroutine that you can call passing the Range V6, V7 or V8 value and any other arguments.

1 answer

Your answer

Sam of Simple Samples 5,586 Reputation points

2021-08-09T21:06:47.86+00:00

I dunno. If you can do all three things in one loop then that would work. Or perhaps you can make a subroutine that you can call passing the Range V6, V7 or V8 value and any other arguments.

Answer 1

Bob Larson (Access MVP 2008, 2009, 2011) 251

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

Share via

how to shorten this code

1 answer

Your answer