Filling alternate cells with two colours?

[TheWennerWoman]

I've got about 1 row x 250 columns of data which are coloured alternately to make things easier to read. If I insert a column half way along, I have to go through and manually change the cell colours. I started off with this but haven't managed to finish it

Sub alternate_colours()
a = 334 ' column LV
b = 571 ' column UY
Cells(4, a).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
Cells(4, a + 1).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
End Sub

I could wrap a DO....WHILE loop around to quit when the value of b is reached.

My query is: some of the cells in row 4 are merged so simply adding 1 to the variable a each time is going to error at some point. Is there a way of doing this?

Thanks in advance.

 

[Joe4]

Why are you using VBA to do this?
I think you can use the ISEVEN and ISODD functions along with the COLUMN() function to do what you want with Conditional Formatting (i.e. =ISEVEN(COLUMN()), =ISODD(COLUMN())).

And I highly recommend getting rid of the merged cells. They are an abomination that cause nothing but issues for things like VBA, sorting, etc.
They are probably the single worst feature of Excel, and experienced Excel programmers know to avoid them like the plague!

If merging cells across multiple columns in a single row, it is far better to use the "Center Across Selection" formatting option, which gives you the same visual effect without all the issues merged cells cause. See here for details: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[TheWennerWoman]

Anyone else have a VBA solution?

"Centre Across Selection" doesn't work work for this because it doesn't keep cells 5, 6 and 7 the same format if they're merged.

 

[Joe4]

Do the cells really need to be merged?
Can you unmerge them and widen the columns?

I am not kidding when I tell you how problematic merged cells can be, especially for things like VBA.
To be honest, I know that many people on this board will not even attempt to answer questions involving merged cells - that is how much people try to avoid them.

If you need to keep the merged cells, I think you will need to check each and every cells to see if it is part of a merged cell; something like the code shown in the responses here:

If Statement: How to test if a cell is merged or not?

Hi all! I am looking to do something like this: If mergecells=True (then do one thing) Else (do something else) Endif But I cannot figure out how to get VB to work with my logic. Any ideas?

www.excelforum.com

However, this could get tricky if you have back-to-back merge areas, i.e. A-C are merged and D-F are merged, as then all cells in the range A-F are in merged cells, though it is two different merge ranges. As you can see, this has the potential to get a bit messy. I am not even sure how to accomplish this.