Copying Conditional Formatting with relative values

[weeksy]

Hi All

I've been working on a spreadsheet with formulas in several columns being used to out put text or a date. In addition to this, I've used conditional formatting to colour the cells based on the values in the cells, or other cells in the spreadsheet. Due to the complexity of the sheet, I prepped everything into my first row (row 4) and now I'm ready to copy that row into other rows.

Here is an example of my rules in row 4:

I highlighted row 4 did a CTRL+C and CTRL+V across a further 100 rows. I expected my conditional formatting rules to update the cell references accordingly. Row 5 was correctly updated so that all the rules showed a cell in row 5:

The problem is that for rows 6-100 all of the conditional formatting rows were referencing cells in row 5. It's like 1 row was updated and then everything after that used the same rules. It's really odd.

If I copy and paste one row at a time, then the rules are updated correctly, but it will take ages to do this for hundreds of lines.

Am I missing something? Is there a better way that I can copy the row and have the conditional formatting update correctly?

Many thanks

Hilary

 

[weeksy]

In Addition to the above, I've realised that in a column where I only have one rule (=$L4="N/A"), even when copying each row individually, the rule remains static as =$L4="N/A".

 

[awoohaw]

Hilary,
CF cell references work the same as regular references.
MyOnlineTrainingHub has a great video on how you can get CF done easily.

If this doesn't help. Please post a sample of your data using the xl2bb add in and someone will work on it, i'm sure.

 

[weeksy]

Hi, It is pretty helpful thank you. Using the example in the video, can I ask what you'd expect to see if you went to Conditional Formatting > Manage Rules when only cell C11 was selected? Would you expect it to show =$C11<=$E$1 or would you expect to see the original formula =$C2<=$E$1?

 

[Joe4]

Hi, It is pretty helpful thank you. Using the example in the video, can I ask what you'd expect to see if you went to Conditional Formatting > Manage Rules when only cell C11 was selected? Would you expect it to show =$C11<=$E$1 or would you expect to see the original formula =$C2<=$E$1?

It will typically show the original formula (the formula applied to the first cell in the Conditional Formatting range).
It can be a little confusing, but you want to look at it in conjunction with the "Applies to" range.

Here is a little example I did. I formatted the range B1:B5, then went to cell B3 and brought up the Conditional Formatting rules, and this is what I see:

So it is not showing me the formula for cell B3, per se. It is showing me the formula for the CF range that cell B3 is found in, and is showing me the formula for the first cell in that range (B1).

Once you understand that, it makes a little more sense.

 

[awoohaw]

It depends on how you set the conditional formatting. If you did as she did in the video and select cell C11 and select manage rules it will show =$C2<=$E$1.
If you apply the CF cell by cell you'll see multiple rules (if so, in this case as many as 30 rules that all do the same thing)

Here is her example, but I've changed the dates. As you can see I've highlighted C11 to look at the rule for that cell's conditional formatting. The rule is the same for all.

 

[weeksy]

It will typically show the original formula (the formula applied to the first cell in the Conditional Formatting range).
It can be a little confusing, but you want to look at it in conjunction with the "Applies to" range.

Here is a little example I did. I formatted the range B1:B5, then went to cell B3 and brought up the Conditional Formatting rules, and this is what I see:

View attachment 106920

So it is not showing me the formula for cell B3, per se. It is showing me the formula for the CF range that cell B3 is found in, and is showing me the formula for the first cell in that range (B1).

Once you understand that, it makes a little more sense.

Brilliant thanks! I think this has been a major contributor to my confusion. Now I know this, and having watched the video in the other contributor's post, I have also worked out how to create new rows which have all of the same rules. Thanks

 

[Joe4]

Glad we were able to help!

From own personal experience, I have had issues trying to copy/paste cells with Conditional Formatting and/or using the Format Painter top copy Conditional Formatting.
It does not always seem to have the intended results for me.
So I typically like to select the whole range I want to apply my Conditional Formatting to first, and then write the rules and have them applied to the whole range at once (by writing the formula for the first cell and letting Excel adjust it for the rest).

 

[awoohaw]

Brilliant thanks! I think this has been a major contributor to my confusion. Now I know this, and having watched the video in the other contributor's post, I have also worked out how to create new rows which have all of the same rules. Thanks

Happy to help.
Best wishes!

 

[weeksy]

Highlighting my first row which has the rules applied, plus all other rows I want to add it to, then CNTRL+D seems to have worked for me.