Using Conditional Formatting to verify data

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hi All!

We track incoming construction contracts using excel, but lately there have been a lot of contracts with the wrong address on them.

I would like it so when we enter the owners name, excell will reference our master sheet and turn red if the address does not match.

So, I have a master like this:

Owner 1 : 123 Fake St.
Owner 2 : 456 Fake St


And if my staff enters Owner 1, with 456 Fake St. It will turn red, and let them know something's wrong.

I thought it was Vlookup in conditional formatting, but I can't get it. Maybe I'm going down the wrong path.

Thanks in advance!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
The probable reason you were getting values highlighted at random, is that the formula was doing an approximate match, rather than an exact match.
Hi Fluff,

Quick question *(I hope) Some owners have multiple property addresses. This formula finds the first one, and says it's correct. Everyone after that will light up red.

Owner 1 : 123 BLUE Street
Owner 2: 123 Fake Street
Owner 1: 345 GREEN Street

In the above example, Owner 1 owns 2 properties, but If I type owner 1, only the BLUE street will be correct. The GREEN street will format red.

Possible to make this formula acknowledge owners that have multiple addresses? THANK YOU!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 13 third street
21Owner 22 second streetOwner 16 third street
22Owner 13 third streetOwner 11 first street
23Owner 44 fourth street
24
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M22Expression=ISNA(MATCH(M20,FILTER($J$20:$J$23,$I$20:$I$23=L20),0))textNO
 
Solution

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 13 third street
21Owner 22 second streetOwner 16 third street
22Owner 13 third streetOwner 11 first street
23Owner 44 fourth street
24
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M22Expression=ISNA(MATCH(M20,FILTER($J$20:$J$23,$I$20:$I$23=L20),0))textNO
That worked!

Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top