G'day Bernie,
Sorry I haven't replied sooner but I was sent up north without a internet
connection
((( & only got back today.
Anyway, this is what I have to divide the runs in the module at the moment.
Public Const xlCIBlack As Long = 1
Public Const xlCIWhite As Long = 2
Public Const xlCIRed As Long = 3
Public Const xlCIBrightGreen As Long = 4
Public Const xlCIBlue As Long = 5
Public Const xlCIYellow As Long = 6
Public Const xlCIPink As Long = 7
Public Const xlCITurquoise As Long = 8
Public Const xlCIDarkRed As Long = 9
Public Const xlCIGreen As Long = 10
Public Const xlCIDarkBlue As Long = 11
Public Const xlCIDarkYellow As Long = 12
Public Const xlCIViolet As Long = 13
Public Const xlCITeal As Long = 14
Public Const xlCIGray25 As Long = 15
Public Const xlCIGray50 As Long = 16
Public Const xlCIPeriwinkle As Long = 17
Public Const xlCIPlum As Long = 18
Public Const xlCIIvory As Long = 19
Public Const xlCILightTurquoise As Long = 20
Public Const xlCIDarkPurple As Long = 21
Public Const xlCICoral As Long = 22
Public Const xlCIOceanBlue As Long = 23
Public Const xlCIIceBlue As Long = 24
'Public const xlCIDarkBlue As long = 25
'Public const xlCIPink As long = 26
'Public const xlCIYellow As long = 27
'Public const xlCITurquoise As long = 28
'Public const xlCIViolet As long = 29
'Public const xlCIDarkRed As long = 30
'Public const xlCITeal As long = 31
'Public const xlCIBlue As long = 32
Public Const xlCISkyBlue As Long = 33
Public Const xlCILightGreen As Long = 35
Public Const xlCILightYellow As Long = 36
Public Const xlCIPaleBlue As Long = 37
Public Const xlCIRose As Long = 38
Public Const xlCILavender As Long = 39
Public Const xlCITan As Long = 40
Public Const xlCILightBlue As Long = 41
Public Const xlCIAqua As Long = 42
Public Const xlCILime As Long = 43
Public Const xlCIGold As Long = 44
Public Const xlCILightOrange As Long = 45
Public Const xlCIOrange As Long = 46
Public Const xlCIBlueGray As Long = 47
Public Const xlCIGray40 As Long = 48
Public Const xlCIDarkTeal As Long = 49
Public Const xlCISeaGreen As Long = 50
Public Const xlCIDarkGreen As Long = 51
Public Const xlCIBrown As Long = 53
Public Const xlCIIndigo As Long = 55
Public Const xlCIGray80 As Long = 56
Sub Share()
Dim nPosties As Long
Dim aryColours
Dim iLastRow As Long
Dim cSharedAddresses As Long
Dim cAddresses As Long
Dim cSpread As Long
Dim iColour As Long
Dim iAddresses As Long
Dim i As Long
aryColours = Array(xlCIRed, xlCIGray25, xlCILightGreen, xlCILightBlue, _
xlCIRose, xlCILime, xlCICoral, xlCISkyBlue, _
xlCIOrange, xlCIPlum, xlCIPaleBlue, xlCIGreen, _
xlCIYellow, xlCIPink, xlCITurquoise, xlCIIvory, _
xlCIOceanBlue, xlCIRose, xlCILavender, xlCITan, _
xlCILightBlue, xlCIGold, xlCITeal, xlCILightYellow, _
xlCIBrown, xlCILightYellow, xlCIAqua,
xlCILightOrange, _
xlCIPeriwinkle, xlCIBlueGray, xlCISeaGreen,
xlCIIndigo)
nPosties = Range("J2").Value
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
cSharedAddresses = Int((iLastRow - 2) / nPosties)
cAddresses = cSharedAddresses
cSpread = nPosties - (iLastRow - 2 - cAddresses * nPosties)
iColour = 1
iAddresses = 1
For i = 3 To iLastRow
Cells(i, "A").Resize(, 6).Interior.ColorIndex = aryColours(iColour -
1)
If iAddresses = cAddresses Then
iColour = iColour + 1
If iColour > nPosties Then
iColour = nPosties
End If
iAddresses = 1
Else
iAddresses = iAddresses + 1
End If
If cSpread = iColour And cAddresses = cSharedAddresses Then
cAddresses = cAddresses + 1
End If
Next i
End Sub
Cheers
Mark