looping help?

Sabiaeric

New Member
Joined
Nov 6, 2017
Messages
8
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?
 
Last edited:
Upvote 0
I'd suggest something like this:

Code:
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:

Code:
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?
 
Last edited:
Upvote 0
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

A
B
C
D
E
120
Number of Orders
number of techs
orders per tech
156
526
30
18
125
2151
2131
33
1313
26
13213

<tbody>
</tbody>
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top