# Conditional Format Alternate Columns

• Hi All,

Is there a way to conditional format a range (say B10:Z100) so that alternate cells in that row are different fill colors (i.e light blue, dark blue across the range but identical colors on all rows)
This condition would only apply if cell B9:B100 has any data in the cells

Here's hoping

Thanks folks.

• try using these formulas after selecting B10:Z100

=MOD(ROW(),2)=0 and choose light blue

=MOD(ROW(),2)=1 and choose dark blue

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Hi and thanks for the reply.

These formula, format an entire row ! whereas I need to format each cell in a row alternately (i.e. B10=light blue, B11= Dark Blue, etc)

Sorry if I was not clear enough in my explanation.

Thanks

• Hi,

What can be done for Rows ... can be done for Columns ...:wink:

Based on NBVC's suggestion :

=MOD(COLUMN(),2)=0 and choose light blue

=MOD(COLUMN(),2)=1 and choose dark blue

Hope this will help ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hey Carim, appreciate the help here

Roy, great advice as ever but opted with Carim's proposal as the range is not formatted as a table.

Thanks guys

• Noticed a problem here folks, I only need the format to be applied when data is present in the range B9:B100, otherwise format is white fill

Thanks

• ok, modified with =IF(COUNT(\$B9:\$OZ100)>0,MOD(COLUMN(),2)=0

Seems to work !

Thanks again

• Glad to hear you could fix your problem ...:wink:

Thanks ... for your Thanks ... :smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim,

To take this a step further,
Is it possible to format 5 columns alternately in this way i.e. D1:D5 = light blue D6:D10 = Dark blue

Thank you

• Hi Guys,

Sample file attached

## Files

• Hi Roy

Well it look great but does create me a few challenges.
Firstly, I'm not sure how you created the table and banded columns options, so I need to research that, but a quick run down here would help if you have time.

Secondly, I now need to make this work with other conditional formatting options as per the example where borders are added when data is present...

Many thanks for your help so far

• Thanks Roy,

Much appreciated

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!