Shading Every 3 Column Headers

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
I have a header row where I want to alternate the shading of every 3 columns within a specific range of columns.

For example,
A1, B1, C1 Shaded
D1, E1, F1 Not Shaded
G1, H1, I1 Shaded
J1, K1, L1 Not Shaded
M1, N1, O1 Shaded

Is there a way to do this WITHOUT having to enter each cell for shading into the VBA code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,615
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    For Counter = 9 To 119 Step 6
      'Set color to BlueLight
      wsConsolidate.Cells(2, Counter).Resize(, 3).Interior.Color = BlueLight
    Next
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,934
Office Version
  1. 365
Platform
  1. Windows
That's what I've read elsewhere too, but I've yet to experience a noticeable lag whenever I've used Loops.
If you are just applying them to a small range where the number of loops is low, it probably isn't too noticeable.
But if you started getting in to the hundreds, thousands, or more, it will become more noticeable.

And of course the other danger with loops is if you aren't careful, you can get stuck in an infinite loop (that really sucks!).
The "For each..." option usually avoids that though, as you are limiting it to some specific set of data.
 

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
If you are just applying them to a small range where the number of loops is low, it probably isn't too noticeable.
But if you started getting in to the hundreds, thousands, or more, it will become more noticeable.

And of course the other danger with loops is if you aren't careful, you can get stuck in an infinite loop (that really sucks!).
The "For each..." option usually avoids that though, as you are limiting it to some specific set of data.
Oh, hell, I guess you win, Joe.

Sorry, Fluff. I was finally able to get the code to splash out color but I can't get the alignment right. I tried playing with the Step number and Resize number (and whoops! with Cells number), but since I don't know the nuance of how the code works (without spending some time digging into it), I'll have to move on with the CF option.

Thanks, gentlemen. I appreciate the assist.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,713
Messages
5,637,927
Members
416,993
Latest member
ant8989

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