Colour rows and add border lines

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
701
Office Version
  1. 2016
Platform
  1. Windows
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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 .
 
Upvote 0
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 .
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,962
Messages
6,105,105
Members
447,947
Latest member
OX_2005

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