conditional formatting

[Rene]

Can you auto fill condtioning formatting in excel 2007? fill handle?

Looking to color code an indv cells across rows.
color code a2 if a2 >a1*.33

a b c
1 200 100 50
2 25 50 10
200*.33=66 100*.33=33 50*.33=16
no color code color code no color code
25<66 50>33 10<16

Or do I need to format each cell a2:z2...

Thanks

 

[Shane Devenshire]

Hi,

Sure. In addition you can just select the entire range and define the
condition for the first cells (keeping things relative). Or you can use the
format painter to copy the CF across the row.

 

[T. Valko]

You can apply the formatting in a single operation.

If your range to format is A2:Z2

Select the *entire* range A2:Z2 starting from cell A2. A2 will be the active
cell. The active cell is the single cell in the selected range that *is not
shaded*. The formula will be entered relative to the active cell. The cell
references will automatically adjust for the other cells in the range.

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=A2>A1*0.33
Click the Format button
Select the desired style(s)
OK out

 

[Rene]

Excellent! Can I find/replace all *.33? I know how to replace all text and
formulas but don't know how to replace conditional formatting.

Thanks again.

 

[T. Valko]

If *.33 are in formulas then Excel would have entered the .33 as 0.33.

On the Home tab>Editing group>Binoculars icon>Replace
Find what: ~*0.33
Replace with: enter what you wish to replace it with but don't enter the
tilde ~
Replace All

 

[Rene]

Excel can't find *0.33 in the worksheet. Its part of the conditional
formatting not part of a formula or data.

I know how to change/find formulas by ctrl ~ >replace whatever but can't
find *0.33 in the worksheet. Too bad there's nothing in the conditional
formatting rules manager for replacing all.

Thanks for your help.

 

[T. Valko]

To change *0.33 in the CF all at the same time:

Select the *entire* range of cells the formatting is applied to.
Goto Home tab>Styles>Conditional Formatting>Manage rules
Select the rule that applies>Edit Rule
Make the change
OK out

 

[Rene]

Thanks. Unfortunately I want to make the same change to many rules. I'll
change them one at a time

 

[T. Valko]

Ok, good luck!

P.S. - I'm not a big fan of the new user interface for conditional
formatting!