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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Use Conditional Formatting.

Select the range A1:O1 and use this CF formula:
Excel Formula:
=ISEVEN(INT((COLUMN()-1)/3))
and choose your shading color option.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,536
Office Version
  1. 365
Platform
  1. Windows
Do you need VBA, this could easily be done with conditional formatting, using this formula
Excel Formula:
=ISEVEN(INT((COLUMN()-1)/3))

Beaten 2it
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, must be VBA.
Turn on your Macro Recorder, and manually do the steps I outlined for you above, and then stop the Macro Recorder.
You with then have VBA code that sets up that Conditional Formatting.
 
Solution

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
Turn on your Macro Recorder, and manually do the steps I outlined for you above, and then stop the Macro Recorder.
You with then have VBA code that sets up that Conditional Formatting.
I'll give it a try, but curious, like the code below for every other one,

VBA Code:
    Dim Counter As Integer
   'For every row in the range
    For Counter = 1 To wsConsolidate.Range("A1:Z1").Columns.Count
        'If the row is an odd number (within the range)
        If Counter Mod 2 = 1 Then
            'Set color to BlueLight
            wsConsolidate.Range("A1:Z1").Columns(Counter).Interior.Color = BlueLight
        End If
    Next

Isn't there code for every other three?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,536
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try
VBA Code:
For counter = 1 To 26 Step 6
   Cells(1, counter).Resize(, 3).Interior.Color = bluelight
Next Counter
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Just note that Loops are notorious resource hogs and are slow.
So avoiding them whenever possible will improve your VBA code and performance (which is why setting up the CF with VBA may be the better option).
 

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
Try
VBA Code:
For counter = 1 To 26 Step 6
   Cells(1, counter).Resize(, 3).Interior.Color = bluelight
Next Counter
No joy. It's not a huge difference, but let me give you my actual code:

VBA Code:
   'Header Alternating Shading of Questions
    Dim Counter As Integer
   'For every row in the range
    For Counter = 1 To wsConsolidate.Range("I2:DO2").Columns.Count
        'If the row is an odd number (within the range)
        If Counter Mod 2 = 1 Then
            'Set color to BlueLight
            wsConsolidate.Range("I2:DO2").Columns(Counter).Interior.Color = BlueLight
        End If
    Next

Maybe I am plugging your solution wrong into my existing code.
 

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
Just note that Loops are notorious resource hogs and are slow.
So avoiding them whenever possible will improve your VBA code and performance (which is why setting up the CF with VBA may be the better option).
That's what I've read elsewhere too, but I've yet to experience a noticeable lag whenever I've used Loops. However, I'm a huge fan of having options, so I may just tinker with the CF idea if I can't get the Looping code to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,502
Messages
5,636,694
Members
416,935
Latest member
Atulcp

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