Hi All,
Ive got an issue where i want to copy conditional formatting down through a sheet and have the rules upadate themselves incrementally....
Basically i have six boxes in a row with numeric values - then i have 2 cells hidden for each row with min and max values which the conditional formatting compares against.
The top row has cells F10 - K10 and the cells with min and max values are X10 and Y10.
There are three rules associated with F10 - K10 which are:
Values between X10 and Y10 = Green text
Values < X10 = Red text
Values > Y10 = Red text
This works well - however X10 an Y10 values are based on formulas else where on the sheet and there is an alternative version of these cells for each row.... so for row 11 the cells are F11 - K11 and X11 / Y11 and so on....
Obviously if this were a normal formula id highlight F10 - K10 and simply drag down until i had the required amount of rows (F59 - K59) and the forumula would auto update. I've tried this with cells containing conditional formatting- but the rule i have in place dont update.... they just carry over.... so the conditional formatting associated with cells F59 - K59 still compares back to cells X10 and Y10 and not X59 / Y59.
Is there any way to get around this without having to sit there and create three rules for each row?
Ive got an issue where i want to copy conditional formatting down through a sheet and have the rules upadate themselves incrementally....
Basically i have six boxes in a row with numeric values - then i have 2 cells hidden for each row with min and max values which the conditional formatting compares against.
The top row has cells F10 - K10 and the cells with min and max values are X10 and Y10.
There are three rules associated with F10 - K10 which are:
Values between X10 and Y10 = Green text
Values < X10 = Red text
Values > Y10 = Red text
This works well - however X10 an Y10 values are based on formulas else where on the sheet and there is an alternative version of these cells for each row.... so for row 11 the cells are F11 - K11 and X11 / Y11 and so on....
Obviously if this were a normal formula id highlight F10 - K10 and simply drag down until i had the required amount of rows (F59 - K59) and the forumula would auto update. I've tried this with cells containing conditional formatting- but the rule i have in place dont update.... they just carry over.... so the conditional formatting associated with cells F59 - K59 still compares back to cells X10 and Y10 and not X59 / Y59.
Is there any way to get around this without having to sit there and create three rules for each row?
