Conditional Formatting.

[Phuzzy]

I know that this is probably pretty simple but for some reason I cannot get it.


I have selected columns A-I, and put in the following conditional formatting formulas:


=MOD(ROW(),2)=1 */This is to get zebra lines all the way down/*


=$I1="Archived" */This is to strikethrough the entire row if the value of I in that row is set to Archived */


What I didn't know is that this second condition seems to be based on the first so my results are that rows that aren't Zebra striped and contain the value Archived in column I are only getting the strikethrough treatment.


How can I modify this so that all rows will have the strikethrough if Archived is found.


It is Excel 2003.


Thanks in advance.

 

[Smallman]

Hi Phuzzy


The trick with this is where you position your rule. You want your Archived Rule to be below your 'Zebra' rule. In XL 03 you should be able to go in and change the order so zebra is the top rule and Archive is the second rule. This will give you the result you are looking for.


Take care


Smallman

 

[Phuzzy]

My archive rule IS below the Zebra rule. Only the lines unaffected with the Zebra rule and contain Archive get the strikethrough rule applied.

 

[Smallman]

Hi Phuzzy


I pretty much use XL10 exclusively these days so get a bit complacent when dealing in earlier versions of XL. Intuitively it should work, in practice you are correct, it doesn’t. I just went over to a colleague who has XL 03 and nutted it out. It is not as straight forward as I thought it would be though. You need 3 conditions.


Condition 1


=IF(AND(MOD(ROW(),2),A1="Archived"),1,0)


The colouring for this condition is background colour Blue with Text Struck through


Condition 2


=MOD(ROW(),2)


The colouring for this condition is background colour Blue


Condition 3


Cell Value ="Archived"


With the format being Strike Through for the text.


Pay very close attention to the above as I have this working at this end. I hope you can do the same.


Take care


Smallman