Highlight a cell that does not match another cell

A

AussieWayne

New Member
Joined
Nov 22, 2012
Messages
28
Hi
I am trying to achieve the highlighting of the cells in Column C that do not contain the same address that appears in Column A
This will highlight those property owners that are absentee which is the ultimate aim. Unfortunately the raw data comes exactly as shown below which means that all cells do not exactly match each other although the addresses at Lines 1, 3, 5, and 7 are obviously the same matching addresses and the addresses at 2, 4, 6, 8 and 9 are the absentee owners that I am trying to highlight.
Does anyone know how I can achieve this? I have been trying to use Conditional Formating but have not been successful.
I have attached a mockup of the data I am trying to work with.
Kind regards
Wayne



A B C


[TABLE="width: 456"]
<COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7826" width=214><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 196pt; mso-width-source: userset; mso-width-alt: 9545" width=261><TBODY>[TR]
[TD="width: 214, bgcolor: transparent"]Property Address[/TD]
[TD="width: 132, bgcolor: transparent"]Owner[/TD]
[TD="width: 261, bgcolor: transparent"]Owners Address[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]1 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 99999 Northville NSW 1007[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]3 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]44 Middle Street Middlesville 9071 USA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]5 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 666 Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]7 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]25 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]C/- Bloggs Super Fund 1 Ray Street Rayville[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
AussieWayne said:
Hi
I am trying to achieve the highlighting of the cells in Column C that do not contain the same address that appears in Column A
This will highlight those property owners that are absentee which is the ultimate aim. Unfortunately the raw data comes exactly as shown below which means that all cells do not exactly match each other although the addresses at Lines 1, 3, 5, and 7 are obviously the same matching addresses and the addresses at 2, 4, 6, 8 and 9 are the absentee owners that I am trying to highlight.
Does anyone know how I can achieve this? I have been trying to use Conditional Formating but have not been successful.
I have attached a mockup of the data I am trying to work with.
Kind regards
Wayne



A B C


[TABLE="width: 456"]
<tbody>[TR]
[TD="width: 214, bgcolor: transparent"]Property Address[/TD]
[TD="width: 132, bgcolor: transparent"]Owner[/TD]
[TD="width: 261, bgcolor: transparent"]Owners Address[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]1 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 99999 Northville NSW 1007[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]3 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]44 Middle Street Middlesville 9071 USA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]5 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 666 Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]7 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]25 Ocean Drive Ocean Heights Qld 2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]C/- Bloggs Super Fund 1 Ray Street Rayville[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Create a dummy column beside your "owners address"

=IFERROR(IF(SEARCH(SUBSTITUTE(A3,",",""),C3)>0, "MATCH"), "NO-MATCH")

Conditional format this dummy column, highlight those with answer "NO-MATCH"

Not that straight forward, but hope it still help.

Thanks.
 
Upvote 0
Try this one:
Code: 
Sub test()
Dim i As Long, lr As Long
Dim ws As Worksheet
Dim value As Variant
Dim val As Variant
Dim sval As Integer
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1") 'change based on your sheetname
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
      value = Split(Cells(i, 1).value, ", ")
      For val = LBound(value) To UBound(value)
            sval = InStr(1, Cells(i, 3).value, value(val), 1)
            If sval = 0 Then Range("A" & i & ":" & "C" & i).Interior.Color = 65535
      Next
Next
Application.ScreenUpdating = True
MsgBox "Done Macro"
End Sub
 
Upvote 0
cml19722000 said:
Create a dummy column beside your "owners address"

=IFERROR(IF(SEARCH(SUBSTITUTE(A3,",",""),C3)>0, "MATCH"), "NO-MATCH")

Conditional format this dummy column, highlight those with answer "NO-MATCH"

Not that straight forward, but hope it still help.

Thanks.
Click to expand...


Hi I follow the logic behind the approach but I am not sure if this is correct as this is the result I get when I cut and paste the formula into adjacent column.

Where am I going wrong.
Kind regards
Wayne[TABLE="width: 664"]
<COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7826" width=214><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 196pt; mso-width-source: userset; mso-width-alt: 9545" width=261><COL style="WIDTH: 208pt; mso-width-source: userset; mso-width-alt: 10130" width=277><TBODY>[TR]
[TD="width: 214, bgcolor: transparent"]Property Address[/TD]
[TD="width: 132, bgcolor: transparent"]Owner[/TD]
[TD="width: 261, bgcolor: transparent"]Owners Address[/TD]
[TD="width: 277, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]1 Ocean Drive Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 99999 Northville NSW 1007[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]3 Ocean Drive Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]44 Middle Street Middlesville 9071 USA[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]5 Ocean Drive Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]PO Box 666 Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]7 Ocean Drive Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]25 Ocean Drive Ocean Heights Qld 2000[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 Ocean Drive, Ocean Heights[/TD]
[TD="bgcolor: transparent"]Bill Bloggs[/TD]
[TD="bgcolor: transparent"]C/- Bloggs Super Fund 1 Ray Street Rayville[/TD]
[TD="class: xl24, bgcolor: transparent"]#NAME?[/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Upvote 0
lancerj017 said:
Try this one:
Code: 
Sub test()
Dim i As Long, lr As Long
Dim ws As Worksheet
Dim value As Variant
Dim val As Variant
Dim sval As Integer
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1") 'change based on your sheetname
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
      value = Split(Cells(i, 1).value, ", ")
      For val = LBound(value) To UBound(value)
            sval = InStr(1, Cells(i, 3).value, value(val), 1)
            If sval = 0 Then Range("A" & i & ":" & "C" & i).Interior.Color = 65535
      Next
Next
Application.ScreenUpdating = True
MsgBox "Done Macro"
End Sub
Click to expand...
Hi lancer thank you very much for posting this information but I am not that experienced in Excel and would not know exactly what to do with this. Is it something simple or quite involved for a reasonable novice like me.
Kind regards
Wayne
 
Upvote 0
AussieWayne said:
.. the raw data comes exactly as shown ..
Click to expand...
If that is so then I think the suggestion in post #3 should work. Here is a somewhat similar approach but without the helper column. Select C2:C?? and apply the Conditional Formatting shown.

Excel Workbook
ABC
1Property AddressOwnerOwners Address
21 Ocean Drive, Ocean HeightsBill Bloggs1 Ocean Drive Ocean Heights Qld 2000
32 Ocean Drive, Ocean HeightsBill BloggsPO Box 99999 Northville NSW 1007
43 Ocean Drive, Ocean HeightsBill Bloggs3 Ocean Drive Ocean Heights Qld 2000
54 Ocean Drive, Ocean HeightsBill Bloggs44 Middle Street Middlesville 9071 USA
65 Ocean Drive, Ocean HeightsBill Bloggs5 Ocean Drive Ocean Heights Qld 2000
76 Ocean Drive, Ocean HeightsBill BloggsPO Box 666 Ocean Heights Qld 2000
87 Ocean Drive, Ocean HeightsBill Bloggs7 Ocean Drive Ocean Heights Qld 2000
98 Ocean Drive, Ocean HeightsBill Bloggs25 Ocean Drive Ocean Heights Qld 2000
109 Ocean Drive, Ocean HeightsBill BloggsC/- Bloggs Super Fund 1 Ray Street Rayville
CF Addresses
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21. / Formula is =COUNTIF(C2,"*"&SUBSTITUTE(A2,",","")&"*")=0Abc
 
Upvote 0
Peter_SSs said:
If that is so then I think the suggestion in post #3 should work. Here is a somewhat similar approach but without the helper column. Select C2:C?? and apply the Conditional Formatting shown.

CF Addresses

ABC
Property AddressOwnerOwners Address
1 Ocean Drive, Ocean HeightsBill Bloggs1 Ocean Drive Ocean Heights Qld 2000
2 Ocean Drive, Ocean HeightsBill Bloggs
3 Ocean Drive, Ocean HeightsBill Bloggs3 Ocean Drive Ocean Heights Qld 2000
4 Ocean Drive, Ocean HeightsBill Bloggs
5 Ocean Drive, Ocean HeightsBill Bloggs5 Ocean Drive Ocean Heights Qld 2000
6 Ocean Drive, Ocean HeightsBill Bloggs
7 Ocean Drive, Ocean HeightsBill Bloggs7 Ocean Drive Ocean Heights Qld 2000
8 Ocean Drive, Ocean HeightsBill Bloggs
9 Ocean Drive, Ocean HeightsBill Bloggs

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 218px"><COL style="WIDTH: 92px"><COL style="WIDTH: 272px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #92d050"]PO Box 99999 Northville NSW 1007[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #92d050"]44 Middle Street Middlesville 9071 USA[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #92d050"]PO Box 666 Ocean Heights Qld 2000[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #92d050"]25 Ocean Drive Ocean Heights Qld 2000[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #92d050"]C/- Bloggs Super Fund 1 Ray Street Rayville[/TD]

</TBODY>

Conditional formatting
CellNr.: / ConditionFormat
C21. / Formula is =COUNTIF(C2,"*"&SUBSTITUTE(A2,",","")&"*")=0

<TBODY>
[TD="bgcolor: #92d050"]Abc

<TBODY>

</TBODY>
[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Thank you Peter I will try that tonight, I love simplicity.
Kindest regards
Wayne
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
How to clear all "no fill" cells over 2000+ columns (~75 rows deep) VBA
Replies
2
Views
637
timeisanelephant77
T
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
983
AlanY
A
B
  • Question Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T
S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
785
Toadstool
Toadstool

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

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
Back
Top