View Full Version : for loop in vba
jeevan raj
08-09-2021, 01:29 AM
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
please help me shorten this code.
Bob Phillips
08-09-2021, 11:41 AM
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
ElseIf Cells(i, 2).Value = Range("T7").Value Then
Cells(i, "A").EntireRow.Resize(Range("V7").Value, 11).Select
Selection.Interior.ColorIndex = 0
ElseIf Cells(i, 2).Value = Range("T7").Value Then
Cells(i, "A").EntireRow.Resize(Range("V8").Value, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Interior.ColorIndex = 15
sn=cells(6,20).resize(3)
for each it in columns(2).specialcells(2)
for j = 1 to 3
If it = sn(j,1)Then it.offset(,-1).resize(cells(5+j,22),11).interior.colorindex=0
next
next
jeevan raj
08-09-2021, 01:21 PM
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
ElseIf Cells(i, 2).Value = Range("T7").Value Then
Cells(i, "A").EntireRow.Resize(Range("V7").Value, 11).Select
Selection.Interior.ColorIndex = 0
ElseIf Cells(i, 2).Value = Range("T7").Value Then
Cells(i, "A").EntireRow.Resize(Range("V8").Value, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Interior.ColorIndex = 15
thanks Bob,
from your code coloring the rows are missed but i have just tried like this
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
its failed because of V8 have no values but other two lines worked well
thanks for your support.
Mogzie
08-15-2021, 03:59 PM
For testloop=6 to 8
valueabc = Range("V" & testloop)
Last = Cells(Rows.Count, 2).End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, 2).Value) = Range("T" & testloop) Then
Cells(i, "A").EntireRow.Resize(valueabc, 11).Select
Selection.Interior.ColorIndex = 0
End If
Next i
Selection.Activate
Selection.Interior.ColorIndex = 15
Next testloop
Can't you use something as simple as this?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.