Conditional formatting: Bulk rows (approximately 150 rows)

[muna]

Hi all,

I have spent some time making a dummy spreadsheet so that it is as easy as possible to explain the solution I am looking for.

In my dummy spreadsheet (see attached) I have used conditional formatting. But, I am currently using two conditional formatting rules per row. You can imagine how long this would take if I need to repeat the rule for 150 rows. Is there a quicker way to do this?

In the attachment, ignore the comments about the percentage help required. I will post a seperate thread on that issue as it's a seperate from the conditional formatting help I require. I think this is most sensible of me to do as it will help with the site's search function and possibly other users.​

 

[muna]

Thank you for your help in advance.

 

[GraH - Guido]

Hi,
Yes, in your CF rule, refer to a column range, not individual cells. Something like =$N$18:$n$1000,$Q$18:$Q$1000 will apply to CF to all the rows from 18 until 1000.

 

[muna]

Hi,
Yes, in your CF rule, refer to a column range, not individual cells. Something like =$N$18:$n$1000,$Q$18:$Q$1000 will apply to CF to all the rows from 18 until 1000.

Thank you for your suggestion.

Any ideas on why it doesn't work?

 

[GraH - Guido]

Hi,
Seems I misunderstood your request on where to find differences, it is in columns, not rows.
Would this be better? I made other CF conditions.

 

[muna]

Hi,
Seems I misunderstood your request on where to find differences, it is in columns, not rows.
Would this be better? I made other CF conditions.

Omg!

It works perfect. Thank you so much!!

Just so I learn what is being done, please could you explain what the attached screenshot formulas are doing?

 

[GraH - Guido]

The first one prevents the second from formatting the cells in the columns O and P, hence the stop if true flag is checked.
The second one verifies if the value in N18 is different from Q18. Notice the relative row reference. This rule will work when "going down". If you would consult the rule when cell N19 is activated, if will show up as N19 in the rule.
Since I use this relative cell reference it can work on the full range.

You can do with only the second rule though, and use this in the reference =$N$18:$N$31,$Q$18:$Q$31.

 

[muna]

The first one prevents the second from formatting the cells in the columns O and P, hence the stop if true flag is checked.
The second one verifies if the value in N18 is different from Q18. Notice the relative row reference. This rule will work when "going down". If you would consult the rule when cell N19 is activated, if will show up as N19 in the rule.
Since I use this relative cell reference it can work on the full range.

You can do with only the second rule though, and use this in the reference =$N$18:$N$31,$Q$18:$Q$31.

Thank you for your explanation, very helpful.

I appreciate the help