looping help?

[Sabiaeric]

hello,
i am new to VBA coding so i might be headed in the wrong direction with this code.

i am trying to build a loop that loops and X amount of times or untill the column is empty.

here is what i have so far:
Sub Test1()
'UpdatebyExtendoffice20161222
Dim x As Integer
Application.ScreenUpdating = False
' Set numrows = number of rows of data.
NumRows = Range("i2")
' Select cell a1.
Range("A1").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
Cells(x, 1).Interior.ColorIndex = 37
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub

i am able to loop it once and that is about it.

the idea is to change the background color depending on how many orders each person will get .
so if i have 300 order and 5 people that day then everyone gets 60 orders.

the loop will run 5 times , and highlight 60 orders one color then 60 in another color.

any and all help will be welcome.

 

[Scott Huish]

I don't think you should have to loop more than once. Can you post a sample of your data?
Number of rows can be calculated within VBA. Why do you need to pull a value from I2?
What determines how many people you have?

 

[Eric W]

I'd suggest something like this:

Sub Test1()
Dim NumRows As Long, NumPeople As Long, RowsPerPerson As Long
Dim r As Long, MyColors As Variant

    NumRows = Range("I2")
    NumPeople = Range("I3")
    MyColors = Array(37, 3, 8, 4, 5, 6, 7, 9)
    
    RowsPerPerson = NumRows \ NumPeople
    
    For r = 0 To NumPeople - 1
        Cells(r * RowsPerPerson + 1, "A").Resize(RowsPerPerson).Interior.ColorIndex = MyColors(r)
    Next r
        
End Sub

You can have the macro figure out how many rows there are with this line instead:

NumRows = Cells(Rows.Count, "A").End(xlUp).Row

Also, what do you want to do if the number of people doesn't divide evenly into the rows?

 

[Sabiaeric]

its a simple data sheet.
column A will hold all the data that need to be sent out
C2 will calculate how many orders are in column A
D2 is the number of tech for the day
E2 will take C2 / D2 = how many each tech gets
it looks like the below table

c and d have changing values depending on the day.

and then color coding every group of , in this example, 18 order in column A with different color

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]120
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Number of Orders
[/TD]
[TD="align: center"]number of techs
[/TD]
[TD="align: center"]orders per tech
[/TD]
[/TR]
[TR]
[TD="align: center"]156
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]526
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]18
[/TD]
[/TR]
[TR]
[TD="align: center"]125
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2151
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2131
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1313
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13213
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

This slight variation of my macro from post # 3 matches your layout. Put whatever colorindexes you want in the MyColors array. The colors will start to repeat once you go through the list once.

Sub DoColors()
Dim NumRows As Long, NumPeople As Long, RowsPerPerson As Long
Dim r As Long, MyColors As Variant

    NumRows = Range("C2")
    NumPeople = Range("D2")
    MyColors = Array(37, 3, 8, 4, 5, 6, 7, 9)
    
    RowsPerPerson = WorksheetFunction.RoundUp(NumRows / NumPeople, 0)
    Columns("A:A").Interior.ColorIndex = 0
    
    For r = 0 To NumPeople - 1
        Cells(r * RowsPerPerson + 1, "A").Resize(RowsPerPerson).Interior.ColorIndex = MyColors(r Mod (UBound(MyColors) + 1))
    Next r
        
End Sub

 

[Sabiaeric]

PERFECT!

thanks everyone!

 

[Eric W]

Glad we could help!