How to use VBA to color cell interiors yellow if cell.value in Address table matches cell.value in Postal Town table?

[dougmarkham]

Hi Folks,

I'm looking at a variety of ways (using VBA) to colour cells in an address table whose values match values postal towns in another table.

There are two tables:
AddrTable (contains 10 columns)
PostalTownTable (one column)

As the address data in AddrTable can run into many thousand rows; therefore, I'm looking for advice on what the most efficient approach might be to achieve this?

Kind regards,

Doug.

 

[ChrisFoster]

Are both tables within the same workbook?

 

[dougmarkham]

Are both tables within the same workbook?

Hi Chris,

Same workbook, different worksheets:
ws1 = AddressData contains tbl1 = AddrTable
ws2 = PostalTowns contains tb2 = PostalTownTable

Kind regards,

Doug.

 

[ChrisFoster]

Ok, conditional formatting will do it without the need for VBA depending on what version of excel you're using. Sorry I should have asked to begin with, what version are you using?

 

[dougmarkham]

Ok, conditional formatting will do it without the need for VBA depending on what version of excel you're using. Sorry I should have asked to begin with, what version are you using?

2016 (365).

 

[Fluff]

Are you using 2016 or 365, they are not the same?
Also you can update your account details to show this information & save members from having to ask.

 

[dougmarkham]

Are you using 2016 or 365, they are not the same?
Also you can update your account details to show this information & save members from having to ask.

Hi Fluff,

The version is 16.0.11929.20776
I thought we were on a 365 licence, but there is no indication of this in the program folder.
However, in the Account information, the following is under: Subscription Product

Microsoft Office 365 ProPlus

The excel.exe file is in C:\Program Files (x86)\Microsoft Office\root\Office16\, therefore, I assume it's 2016.

I'll update my profile accordingly.

Kind regards,

Doug.

 

[ChrisFoster]

Perfect! The following is assuming both sets of postcodes are in column A on both sheets. You'll need to amend the formula to suit the columns your data is in, give me a shout if you need help with that.

Highlight the column on your address table then press conditional formatting --> then press 'New Rule' --> then go to the bottom option to use a formula to determine the cells to format.
Once you've done that enter the following formula into the formula field;

=COUNTIF(Sheet2!$A:$A, $A1)

Now press the format button just above the cancel button, this will allow you to select the colour/font/outline etc you want

Then press ok. That should then change the formatting of any cell that has the same post code in both lists.

 

[Fluff]

Microsoft Office 365 ProPlus

In that case you are on 365 not 2016.

 

[dougmarkham]

Perfect! The following is assuming both sets of postcodes are in column A on both sheets. You'll need to amend the formula to suit the columns your data is in, give me a shout if you need help with that.

Highlight the column on your address table then press conditional formatting --> then press 'New Rule' --> then go to the bottom option to use a formula to determine the cells to format.
Once you've done that enter the following formula into the formula field;

=COUNTIF(Sheet2!$A:$A, $A1)

Now press the format button just above the cancel button, this will allow you to select the colour/font/outline etc you want

Then press ok. That should then change the formatting of any cell that has the same post code in both lists.

Hi Chris,

It's Postal Town's that I'm matching (e.g., STOCKPORT), not post-codes.
The purpose is to identify postal towns, for the following reason:
Within a set of address data, some addresses have more lines than others, so postal towns get mixed up between address lines 3 and 6 (col C to G) depending upon the client.

Thus, I downloaded a copy of all postal towns (listed in column A in worksheets("PostalTowns") in order to identify any postal towns within Worksheets("AddressData").Range("$C:$G").

Is such a thing possible with either conditional formatting or VBA?

Kind regards,

Doug.