Copying Condtional Formatting

[TMILJVIPM]

Guys,

I got a problem that I need some help with. On several of my sheets I use condtional formatting and I just realized that copying the cell with the condtional fromatting doesnt work like it does with the formula. Lets say cell K10 has the formula and the conditonal formatting in it. Well if I copy the cell and paste it from K11:K250 the formula automatically moves with each cell like it should but the conditional formatting is still linked to cell K10. Is there a way to copy the conditional formatting and paste it in all the cells and make it apply to that specific cell?

 

[grizz]

after you right click your cell and copy -- highlight all cells you want and right click and Paste Special / click formats and then OK

 

[TMILJVIPM]

after you right click your cell and copy -- highlight all cells you want and right click and Paste Special / click formats and then OK

Its still doing the same thing. Cell K10 has this formula:
=if(I10="","",I10-J10) and then the condtional format I have set with the 3 icon circles is green >=$I$10*.2

In cell I10 I have 100 - Cell J10 81 and the yellow icon appears like its suppose to

but if I go to cell I11 and type in 100 cell K11 automatically shows yellow icon when it should be a green icon.

When I go to conditional formatting - manage rules on cell K11 is refering to cell K10

 

[Aladin Akyurek]

Select the source area with CF.
Apply Format Painter,
Select the destination area.

 

[TMILJVIPM]

Select the source area with CF.
Apply Format Painter,
Select the destination area.

No sir its still doing the same thing. Cells K11:K256 are still refering to the condtional formatting of cell K10. Very frustrating

 

[Norie]

You might need to change the absolute reference in the conditional formatting formula.

If you are copying down and you want the reference in the formula to change according to the row you are copying to then remove the second $.

 

[TMILJVIPM]

You might need to change the absolute reference in the conditional formatting formula.

If you are copying down and you want the reference in the formula to change according to the row you are copying to then remove the second $.

Thanks, but I tried already and it comes up with a message of "cannot use relative references in conditional formatting criteria for color scales, data bars, and icom sets"

There has to be something becuase I have thousands of cells that I need it in and surely I wouldnt have to input the conditional format for each individual cell.

 

[Norie]

Well I've never seen that message.

In previous versions you couldn't use various things, eg array formulas, but definitely not relative references.

Looks like some more constrictions have been applied.

 

[TMILJVIPM]

anyone else got any ideas?

 

[TMILJVIPM]

So is this an impossible task for excel 2007?????

Where are the MVP's at