Colour rows and add border lines

[Sunline]

Hello all , as per my example sheet im wanting to colour shade these rows standard colour blue and light green .

Col W shows what the result would look like if cells A2:V27 were shaded in these colours and heavy bold lines added .

Col S always contains a number from 3 too 18 , This number is random , there is no order .

If cell S28 said 15 then there would be 15 rows to either colour blue or light green .

In col V 1 or 1= will always be colour blue or light green so hopefully a macro will follow suit to colour and add the border lines .
Thanks .

Excel Workbook
	A	O	P	Q	R	S	T	V	W
1	ID	Class	Dist	Stakes	R#	Fsz		Placing
2	1	OPN HCP	1200	15,000.00	1	5	5	1
3	2	OPN HCP	1200	15,000.00	1	5	2	2
4	3	OPN HCP	1200	15,000.00	1	5	1	3
5	4	OPN HCP	1200	15,000.00	1	5	3	4
6	5	OPN HCP	1200	15,000.00	1	5	4	5
7	6	MDN HDL	2760	12,500.00	2	14	11	1
8	7	MDN HDL	2760	12,500.00	2	14	1	2
9	8	MDN HDL	2760	12,500.00	2	14	2	3
10	9	MDN HDL	2760	12,500.00	2	14	3	4
11	10	MDN HDL	2760	12,500.00	2	14	14	5
12	11	MDN HDL	2760	12,500.00	2	14	5	6
13	12	MDN HDL	2760	12,500.00	2	14	10	7
14	13	MDN HDL	2760	12,500.00	2	14	7	8
15	14	MDN HDL	2760	12,500.00	2	14	9	9
16	15	MDN HDL	2760	12,500.00	2	14	8	10
17	16	MDN HDL	2760	12,500.00	2	14	13	11
18	17	MDN HDL	2760	12,500.00	2	14	6	12
19	18	MDN HDL	2760	12,500.00	2	14	4	LR
20	19	MDN HDL	2760	12,500.00	2	14	12	P
21	20	MDN HWT	2000	7,000.00	3	7	1	1
22	21	MDN HWT	2000	7,000.00	3	7	4	2
23	22	MDN HWT	2000	7,000.00	3	7	7	3
24	23	MDN HWT	2000	7,000.00	3	7	5	4
25	24	MDN HWT	2000	7,000.00	3	7	3	5
26	25	MDN HWT	2000	7,000.00	3	7	6	6
27	26	MDN HWT	2000	7,000.00	3	7	2	7
Sheet1

 

[Sunline]

Hello , just realised boarder lines are not recognised when pasteing sheets .

Boarder lines were required above row 2 and bottom of row 6
Boarder lines were required above row 7 and bottom of row 20
Boarder lines were required above row 21 and bottom of row 27

Basically in col S , what ever the number says and the following amount of rows
follow then put a standard heavy boarder line between each race .

Thanks .

 

[Mahesh_P]

Hi
Try :

Sub Mtest()
For i = 3 To Cells(Rows.Count, "G").End(xlUp).Row
Cells(i, 7).Offset(Cells(i, 7).Value, 0).Select
Rows(i).Interior.ColorIndex = 33
i = ActiveCell.Row - 1
Next i
End Sub

Excel Colorplate info [URL]http://dmcritchie.mvps.org/excel/colors.htm[/URL]

 

[Sunline]

Thanks for that Mahesh_P but im getting an error , this line in your code is lighting up yellow
Cells(i, 7).Offset(Cells(i, 7).Value, 0).Select

Not sure what i may be doing wrong .
Thanks .

 

[Mahesh_P]

Hi

base on u r provided file
Try :

Sub Mtest()
On Error Resume Next
Application.DisplayAlerts = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
For i = 2 To Cells(Rows.Count, "S").End(xlUp).Row
Cells(i, 19).Offset(Cells(i, 19).Value, 0).Select
If Cells(i, 19).Offset(0, 1).Value <= 4 Then
Rows(i).Interior.ColorIndex = 10
Range(Cells(i, 41), Cells(i + (Cells(i, 19).Value) - 1, 41)).Interior.ColorIndex = 10
Else
Rows(i).Interior.ColorIndex = 33
Range(Cells(i, 41), Cells(i + (Cells(i, 19).Value) - 1, 41)).Interior.ColorIndex = 33
End If
i = ActiveCell.Row - 1
Next i
Application.DisplayAlerts = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub