Conditional Formatting copy / update

leeb-1977

New Member
Joined
Mar 16, 2011
Messages
4
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,257
Do you have $ symbols in the formula in the conditional formating dialog box ?

For example, the bit that refers to X10, does it actually contain $X$10 ?

If yes, try editing out the second $ symbol, and also try reading up on absolute versus relative cell references.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,937
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top