question

RajeevRaghavan avatar image
0 Votes"
RajeevRaghavan asked BobLarson-6601 answered

how to shorten this code

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

office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

1 Answer

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered


 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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.