hardcoding conditional formating

[mhobbs]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Can I hardcode conditional formatting? I need to copy cell colors to a table the same size with data that does not meet conditional rule.<o></o>
<o></o>
Thanks,<o></o>
<o></o>
Marland<o></o>

 

[Sthrncali]

Unfortunately not... The only thing you can copy to a new cell would be the "logic" that drives the conditional formatting, not the formatting itself.

 

[dd_]

Unfortunately not... The only thing you can copy to a new cell would be the "logic" that drives the conditional formatting, not the formatting itself.

There is a way to copy the actual conditional formatting (hard code the format) if it's formula driven but you'll need to work off a temporary table first. For my example the conditional format I have is =if(A1=0,1,0); if that is met, the cell shaded green.

Steps to hard-code the conditional formatting:

• Copy the conditional formatting formula into a temporary table (same size, etc.)
• Change the formatting formula to be =if(A1=0,"A",1)
• Select the temporary table
• Select only cells that have text (this will select only the cells with A, aka cells that meet the formula criteria). See below if you have questions on how to do this.

• Change the color of the cells
• Voila--hard coded conditional formats that can be paste-special'ed onto the original table

To select only cells that have text do the following:

• Select the table
• hit F5 or Go To
• hit Special
• Select the Formula radio button
• Uncheck each option until only the Text box is checked
• hit okay
• Excel will select only text values from the formula

This was tested in Excel 2007.