Using 2 Conditional Formatting rules on multiple rows

[lembkin]

Hello everyone,

I am working on a budget sheet but I can't seem to easily copy my conditional formatting without having to apply the same formatting (endless clicking) to every new row.

Current Situation: On my budget sheet I have 2 collums working together for my conditional formatting: "Total cost" and "Ammount paid so far". If ammount paid = the total cost the backgroud colour of the cell turns green. If the ammount paid is less than the total cost paid the background colour of the cell is red. I have clicked on the conditional formatting button and applied the rules to do this and it WORKS (picture attached " conditional formatting"), hurrah!, the problem I have is with copying/and paste/paste special with this formula I can't seem to get it to do what I want it to do!

Problem 1: If I just copy and paste the cell content (e.g. C8 and D8) the first cell value does not change (it becomes C8 and D9 - where as I want it to be C9 and D9). How do I solve this PLEASE!

Problem 2 (working with problem 1): When I gave up and resigned myself to clicking in the right conditional formatting into every cell it worked fine until I inserted a new row into the middle of the table - this is something that I will have to do, given the nature of the my worksheet! Adding in a new row changed the formatting of the cell above so it no longer worked and I had to go back and edit it AGAIN, as well as updated the conditional formatting in the newly inserted row. It looked like the picture attached ("conditional formatting 2").

Thanks for reading through my post. I hope you can help. This has now frustrated me for a few hours and I am hoping it will be easy enough to fix to save my hands from more excessive clicking! Thanks in advance.

[DBY]

Hello
When setting your conditional formats, try removing the Absolute references from the C column cells. For example, it should read: C8 not $C$8.

Hope this helps.
DBY

[lembkin]

Ok thanks! Super! that solves problem 1. If I copy and paste into each new cell it works, However, problem 2 still stands.

If I try and copy and paste the formula by pulling down the bottom right hand corner box, OR if I add in a NEW ROW (bigger problem as this will happen) it combines the formulas as shown in "conditional formatting 2" picture posted earlier (instead of just showing =$D$9 into the new row it changes the formula in row 8 and row 9 to =$D$8:$D$9 showing the range, but I dont want it to automatically range these 2 rows. I want it to remain as it was before!). Anyone know how to avoid this?

Thanks in advance.

[DBY]

Sorry things are still not working. I not sure exactly how you're applying your conditonal formats. There's no need to use Copy and Paste Special to apply them. I would just select the cells where you wish to apply the formatting and then apply the rules. For example, if I were comparing Column A 'Amount Paid' against Column B 'Total Cost', I would simply apply the following Conditional formulas to my range of data in Column A:

=A2=B2 for Green

=A2<B2 for Red

If you insert a new row in-between the top and bottom of your range the formats will automatically be applied.

DBY

[lembkin]

Tell me how you would apply your conditional formatting - I think I am going the long way round!

I clicked on the cell in column a that I wanted to change column. I went to the top menu on the home page tab and selected the conditional formatting button and then applied the 2 rules = green, less than red.

I tried to do a several at once but it complained when I used the method above, I hope that makes sense! Thanks for your continued help!

[DBY]

Hello
Take a look at the attached file. It gives a basic idea of how to apply some Conditional formatting as I've understood what you're looking for. Perhaps I'm misunderstanding what you require, if I am, maybe you could upload an example file outlining what you need.

DBY

[scss]

Hello
Take a look at the attached file. It gives a basic idea of how to apply some Conditional formatting as I've understood what you're looking for. Perhaps I'm misunderstanding what you require, if I am, maybe you could upload an example file outlining what you need.

DBY

Hi DBY,
I've just found this thread as I'm having this problem. I'm not able to access the attachment to see your solution. Is it possible to allow me access?

[lembkin]

Thank you very much for this! It now works like a dream. For some reason when I sed the preset rule options on the "conditional formatting2 it was just not as effective as creating a new rule. Now it is fine!

I got confused with my formula, I though it would have to be more complex to apply to a range and change every time.

Thanks for all your help!

---------- Post added at 03:17 PM ---------- Previous post was at 03:16 PM ----------

Thank you very much for this! It now works like a dream. For some reason when I sed the preset rule options on the "conditional formatting2 it was just not as effective as creating a new rule. Now it is fine!

I got confused with my formula, I though it would have to be more complex to apply to a range and change every time.

Thanks for all your help!

[scss]

It's OK. Got it. Thanks