conditional formatting a workbook in 2010

[sarah.so]

hi everyone!

i have looked across the posts and cant seem to find what i want!

i have a workbook excel 2010 which i want to format to return
if any cell = SL turn that cell purple
if any cell = S turn that cell red

but i have 67 sheets i need this doing to! do i have to insert the cf onto every page or is there a quicker way of doing it?

[TMS]

Select all the sheets (group them). Then select all the cells (this will select all the cells on all the sheets). Then apply your CF.

Regards, TMS

[TMS]

You might want to update your profile as it says you are using 2003.

Regards, TMS

[sarah.so]

brilliant - thank you!

i have updated profile too!

[sarah.so]

..i have tried to do as suggested, but for some reason when i select all the sheets i cant get into the conditional format to apply the cf that i want? (its greyed out)

all sheets are unprotected.

the cf was applied to the workbook on every page, but when i added sheets to the workbook yesterday for some reason it lost the CF's that had already been applied?

any ideas?

[TMS]

My apologies. I tested the solution in Excel 2003 ... no problem. However, I've just tried it in Excel 2007 and the CF is greyed out, as you said. Doesn't seem to matter how many sheets are grouped.

I assume this is a constraint introduced in Excel 2007 because of the increased size of the sheets.

The alternative is to record a macro to apply the CF formulae to one sheet and then loop through all the sheets.

Or maybe try it in Excel 2003 and then save as 2007 format? This approach does work and is maybe OK as a one off.

Regards, TMS

[sarah.so]

hmmm i tried to save in 2003 so that i can make the changes.. but the other formats i have are not compatible..
its quite an involved spreadsheet with a lot of formulas and other cf's in it.

im not sure how to record macros.. would a vb code help? and if so how do i do that!?

[TMS]

Try:

Please Login or Register  to view this content.

Note that this deletes existing CF conditions first ... you might want to remove that.

Please TEST ON A COPY of your workbook.

Regards, TMS