Giulia has attended:
Excel Intermediate course
Conditional Formatting
Hi all, I am always using conditional formatting on large tables that contain, for example, a list of people names and to-do tasks.
I have noticed that if I insert new rows after the conditional formatting rule has been set (e.g. to assign a colour to each person) the formula assigning the rule would split and new conditional formatting rules would automatically create.
For example if I insert a COPIED row in row 5, my formula =$B$2:$B$11 would become =$B$2:$B$4,$B$6:$B$12 and a new additional rule would automatically be created: =$B$5.
So if I have added many rows over time and I then realise that I need to adjust my rule, I would need to update several formulas. Is there a way to stop this and make sure that the original formula doesn't get copied/split every time I add a row?
It would be great if you could let me know at gsonzogno@ahmm.co.uk
Thanks in advance
Giulia
RE: Conditional Formatting
Hello Giulia,
Thank you for your forum question. To understand the rule you created a bit better, would it be possible to attach a screenshot of your rule as it shows in the Manage Rules - Edit Rule box? I could then recreate and test it to find a solution. You can send the screenshot to info@stl-training.co.uk.
Kind regards
Marius Barnard
RE: Conditional Formatting
Hi Giulia,
When you copy rows and paste them into another table using the default paste (Ctrl + V) or ‘Insert Copied Cells’ (when you right-click), it splits up your rule like in your example. That’s because the rule doesn’t exist in the new rows.
To make it work, insert blank rows first, then go and copy the data rows you want to paste. Go back to your inserted blank rows. If it’s just values you’ve copied (not formulas), right-click and Paste Values. This will apply your conditional formatting rule to the new rows. If your copied data is formulas, lock the cell references in the formulas before you copy ($ symbols, e.g. $D$5), otherwise the cell references will change. Then go and paste the rows in using right-click, Paste Formulas. This will also apply the conditional formatting rule immediately.
I hope this helps!
Kind regards
Marius Barnard
STL
