Conditional Formatting question

[Salar]

I have a spreadsheet with is approx. 10000 row by12 cloums wide and I am trying to colour band each alternative row for eay reading. The formula I am using, which allows filtering is;

=MOD(SUBTOTAL(3,$A1:$A1),2)

I can insert this in row A and using the format painter I can copy this conditional format down. I have tried to select the whole spreadsheet ctrl "A" and inserting the CF formula but this doesn't seem to work.

Can someone advise an easier way to do this?


Thanks

Salar

 

[crook_101]

Hi,

Highlight all of your data, go to CF and use the following formula:

=MOD(ROW(),2)

This should apply colour to alternative rows of highlighted selection area

Cheers,
Ian.

 

[Salar]

Hi,

Highlight all of your data, go to CF and use the following formula:

=MOD(ROW(),2)

This should apply colour to alternative rows of highlighted selection area

Cheers,
Ian.

Thanks for this Ian, unfortunately this formula does not maintain the format when the data is filtered. Hence my formula whch does not like to be entered using highlighted rows and columns.

Cheers

S

 

[Salar]

Bump


Thanks

Salar

 

[Peter_SSs]

With no CF applied, I would do it this way:

1. Select all the data (or as much of the sheet as you want the CF applied to) with A1 being the active cell.

2. Use this CF formula: =MOD(SUBTOTAL(3,$A$1:$A1),2)

 

[Salar]

Thank you Peter this work perfectly.

With no CF applied, I would do it this way:

1. Select all the data (or as much of the sheet as you want the CF applied to) with A1 being the active cell.

2. Use this CF formula: =MOD(SUBTOTAL(3,$A$1:$A1),2)