Excel: Conditional Formatting at regular intervals in a column? 5

[Adduce]

How can I format a column in Excel to highlight (Fill Color) cells only at specified regular intervals? e.g. In range C1:C200 I would highlight every 3 cells starting at C3, thus highlighting C6, C9, C12, and so on.

 

[xlbo]

For this case, you can use conditional formatting. Highlight all the cells you want to apply this to. Go to Format > Conditional Formatting
1st box Cell Value
2nd Box Is equal to
3rd box =LEN(ROW()/3)<>1

HTH
Geoff

 

[GlennUK]

The conditional formatting you need is actually :

=MOD(ROWS(),3)=0

and make the format ( by pressing the Format ... button ) whatever you want.

Glenn.

 

[xlbo]

Actually, my CF does work - and when I put yours in it highlighted all the rows that weren't every 3rd row (ie 1,2,4,5,7,8 etc)
Please check ;-)
Geoff

ps - I was using the cell value is equal to before the formula - If you use <>, you can turn it round

 

[GlennUK]

I should've made mine more clear ( and checked for mistakes ), should be ...
use menu command Format > Conditional Formatting
and in the first box choose Formula Is
and in the second box put =MOD(ROW(),3)=0
and choose a format for highlighting.

That should work.
Glenn.

 

[xlbo]

And using the Formula= way
mine is =LEN(ROW()/3)=1

Both work but it seems Adduce ain't that bothered ;-)

Geoff

 

[DaleWatson123321]

Hi guys,

In the absence of Adduce, please allow me to be the &quot;Judge&quot; on this. I've checked both out, and here's what I encountered...

With Geoff's option... I tried it on an entire column (by clicking the column letter) - and the formatting ONLY extended down to row 27. I also tried highlighting from Row 1 to 500, and AGAIN it only formatted down to row 27.

In checking the Conditional Formatting in the cells below row 27, they DO have the same conditional formatting formula - BUT these cells below row 27 do NOT show the formatting every 3rd row.

Geoff, I'll still give you a STAR for effort.

THE WINNER, however, has to be Glenn.

Using Glenn's method, it applies in ALL of the above noted situations - i.e. an entire column, or a portion of a column. ALSO, one can copy any portion of the formatted range to another location, and the formatting also gets copied.

Congratulations, Glenn !!! ===> STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

If I'd had my brain switched on, I might even have realised that 30/3 = 10 which has a length of 2 - D'Oh - really don't think I should get a star for that - just shows what happens when you only test on a sample of data
Geoff

 

[Adduce]

The formula =MOD(ROW(),3)=0 worked beautifully. Thanks guys, you have saved me a tremendous amount of time.