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.

 

[dougmarkham]

Hi Fluff & Chris,

I've been searching for a way to do this with VBA all afternoon and found something which might be modifiable.
The following VBA matches:
Column F (Header = Address line 4) of the AddressData worksheet (tbl name: AddrData)
with
Column A (Header = Postal Towns) of the PostalTowns worksheet (tbl name: PostalTowns)

Sub MatchPostalTownsAndColour()
'Highlights matches only in ws("AddressData")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual


Dim shAD As Worksheet
Dim shPT As Worksheet
Dim lr As Long
Dim fVal As Range
Dim c As Range

Set shAD = Sheets("AddressData") 'Edit sheet name
Set shPT = Sheets("PostalTowns") 'Edit sheet name

lr = shAD.Cells(Rows.Count, 6).End(xlUp).Row

For Each c In shPT.Range("PostalTowns[Postal Town]") 'Assumes header row
    Set fVal = shAD.Range("F2:F" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not fVal Is Nothing Then
            fAdr = fVal.Address
            Do
            fVal.Interior.ColorIndex = 27
            fVal.Value = c.Value
            Set fVal = shAD.Range("F2:F" & lr).FindNext(fVal)
            Loop While fVal.Address <> fAdr
        End If

Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

End Sub

How would one go about modifying the code to enable matching against all 5 address columns (cols C to G)?

Kind regards,

Doug.

 

[ChrisFoster]

Sorry for the late reply, yes this still can be done using conditional formatting, you may even then be able to record a macro of you setting the formatting up so it's easier in future if you're always using new data.

Highlight the column C on the AddressData sheet 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(PostalTowns!$A:$A, A1)>0

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 postal town as on the PostalTowns sheet. From there highlight column C and use the Format Painter to paste the format over to columns D:G.

 

[dougmarkham]

Sorry for the late reply, yes this still can be done using conditional formatting, you may even then be able to record a macro of you setting the formatting up so it's easier in future if you're always using new data.

Highlight the column C on the AddressData sheet 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(PostalTowns!$A:$A, A1)>0

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 postal town as on the PostalTowns sheet. From there highlight column C and use the Format Painter to paste the format over to columns D:G.

Hi Chris,

I guess it's sometimes simpler to use excels existing functionality and call it with a macro.
Thank you for your help

Kind regards,

Doug.