Colour rows and add border lines

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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
AOPQRSTVW
1IDClassDistStakesR#FszPlacing
21OPN HCP120015,000.001551
32OPN HCP120015,000.001522
43OPN HCP120015,000.001513
54OPN HCP120015,000.001534
65OPN HCP120015,000.001545
76MDN HDL276012,500.00214111
87MDN HDL276012,500.0021412
98MDN HDL276012,500.0021423
109MDN HDL276012,500.0021434
1110MDN HDL276012,500.00214145
1211MDN HDL276012,500.0021456
1312MDN HDL276012,500.00214107
1413MDN HDL276012,500.0021478
1514MDN HDL276012,500.0021499
1615MDN HDL276012,500.00214810
1716MDN HDL276012,500.002141311
1817MDN HDL276012,500.00214612
1918MDN HDL276012,500.002144LR
2019MDN HDL276012,500.0021412P
2120MDN HWT20007,000.003711
2221MDN HWT20007,000.003742
2322MDN HWT20007,000.003773
2423MDN HWT20007,000.003754
2524MDN HWT20007,000.003735
2625MDN HWT20007,000.003766
2726MDN HWT20007,000.003727
Sheet1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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 .
 

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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

New Member
Joined
May 24, 2012
Messages
47
Hi

base on u r provided file
Try :
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,357
Messages
5,635,797
Members
416,882
Latest member
ericvrealty

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
Top