conditional formatting

[TracyB]

Hi I am using a number of rules in excel here is my example, box M5 has a date written in it, Box N5 has a formula to generate a date in 7 days time to remind me to send an email. N5 has conditional formatting to be green amber, red. I have copied and paste this down both columns so rules and formatting apply. I have column P which is formatted to a yes no drop down. I have managed to make N5 change to purple when P5 changes to yes. If I change the drop down box in P5 to no N5 stays the date color. When I try and paste that rule down all the boxes in column N go off box P5 instead of the box opposite it in Column P. I.e N8 will be purple even though P8 says no. If I change P5 to no all boxes in column N change back to date colors. Is there a way to copy and paste this rule so I dont have to individually do it by box.

[#2 · Accepted solution]

I'm not sure I followed everything you were talking about there, but you can set conditional rules to be ranges, not just cells. So if you apply a rule and set it to A1:A10, all 10 cells will evaluate that rule and apply it on a cell by cell basis. When doing this, you need to pay attention to the formula you are testing for relative or absolute references. Usually you want relative, but sometimes absolute ($B$1) or partially relative. ($B1:$B10) or (B$1:B$10)

[#3]

Hi Ed, you may not have understood but you totally gave me the answer I think because I was putting the dollar sign before the number was limiting it to one cell, so removed it and I can now copy and paste away. Thank you!