Copying conditional formatting with cell references

[hakeae]

I've looked though previous posts but can't see the answer to this one - when a CF rule involves a referenced cell, using format paint doesn't update the cell references (as happens when copying formulae) but maintains the original cell reference in the rule, so the CF in the pasted cells is not correct. What I've been doing is setting up each CF individually which is a drag . There's probably a really simple way to do it ... Thanks for your help.
Example attached: staff can work either 4hr or 8hr shifts but their total hours per month must equal their contracted hours. CF is set up on D7 (?=D4) but format paint to E7 keeps the rule at (?=D4) while I want (?=E4).

 

[David Evans]

Take a look at the attached - If I've properly divined your problem, it may help you ...

 

[Hui]

You have to setup CF formula allowing for relativeness of the functions
So where you have D7 maybe it should be locked to Columns using $D7 or Rows D$7 or both Columns and Rows using $D$7

 

[hakeae]

Take a look at the attached - If I've properly divined your problem, it may help you ...

David, if you look at the rule in the other cells, eg I7, the formula for CF is still referencing E4 & E7:



See my problem :/ ...

 

[hakeae]

You have to setup CF formula allowing for relativeness of the functions
So where you have D7 maybe it should be locked to Columns using $D7 or Rows D$7 or both Columns and Rows using $D$7

OK thanks I'll try this .

 

[hakeae]

OK thanks I'll try this .

Ah smart excel has already done that ...

 

[hakeae]

ha ha here we are a year later and I still can't get it to work ... searching again & found my own post .

 

[bosco_yip]

Ah smart excel has already done that ...
View attachment 29032

Something like this....

 

[hakeae]

ha ha here we are a year later and I still can't get it to work ... searching again & found my own post .

I've found the solution on another website - use Formula rather than Cell Contents algorithms, select all column cells for application of formula, and create formula for first cell using eg "=$A3>=$B3". Excel will apply the formula appropriately to all selected cells. This may be really basic to some of you but it has caused me considerable frustration for some time! Thanks for responding, I appreciate it!