• 397K Members
• 8,253 Online
• 434K Conversations
SOLVED

Conditional formatting en masse

Occasional Contributor

Conditional formatting en masse

Hello! I am trying to accomplish the following process:

If A1 and B1 cell contents don't match, format both A1 and B1

If A2 and B2 cell contents don't match, format both A2 and B2

If A3 and B3 cell contents don't match, format both A3 and B3

...and so on for the entire column

Is this possible to do without adding a new rule for each row? I have thousands of rows to apply this to, and they all need to function independently from each other. Thank you so much for your time!

6 Replies

Re: Conditional formatting en masse

Hi Tyler,

It's like a rule

=A1<>OFFSET(A1,0,1)

Re: Conditional formatting en masse

Thank you for taking the time to respond, Sergei. However, unless I misunderstand, this does not solve my problem. I need to apply this rule to every row in the sheet and have both cells affected by the conditional format. Thank you again! :)

Re: Conditional formatting en masse

I found a solution! (In case anyone else has this problem in the future and stumbles upon this post)

First, highlight A1:B1. Apply conditional formatting with the rule type "Format only unique or duplicate values". Select unique and format as desired.

Then, to apply en masse, but have them function independently, highlight A1:B1 again and double click the Format Painter. After that, hold the down arrow until all desired rows have had the rule applied to them.

If anyone knows of an easier way, please let me know!

Solution

Re: Conditional formatting en masse

IMHO, easier with formula

=\$A1<>OFFSET(\$A1,0,1)

or simply

=(\$A1<>\$B1)

With format painter you generate as many rules as rows in your range.

Re: Conditional formatting en masse

Ignore my last reply, I deleted it. I needed to use the absolute reference to solve my issue. Thank you so much Sergei! This is great.

Re: Conditional formatting en masse

Tyler your method worked fine but you can apply it to the entire column simply by clicking on the column letter at the top after double-clicking format painter. Brilliant.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
205 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies