So unless there is a very limited number of equivalent matches then what you are asking is not realistically feasible

Michael

Solved

Posted on 2011-10-13

Hi Experts,

I am trying to validate data in two columns in Excel. I have some existing code that compares the values of columns A & B of the worksheet and highlights the values that are not matching. What is in column A, but not in column B gets highlighted red in column A and what is in column B but not in A gets highlighted yellow. What I am faced with now is that there are certain values in column A that are equivalent to the values in column B, but they are not the same text. For example; cell A4 may have 'Question1' as the value and cell B16 may have 'Answer1' as the value. I need excel to understand this and not apply conditional formatting. Can someone please tell me what code will I need to add (perhaps another if statement?) to the existing code below to do this?

For Each mycell In wks.Range("A2:A" & lastRowA)

If mycell.Value <> "" Then

matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("B2:B" & lastRowB), 0)

If Err.Number <> 0 Then

mycell.Interior.Color = vbRed

Err.Clear

End If

End If

Next mycell

For Each mycell In wks.Range("B2:B" & lastRowB)

If mycell.Value <> "" Then

matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("A2:A" & lastRowA), 0)

If Err.Number <> 0 Then

mycell.Interior.Color = vbYellow

Err.Clear

End If

End If

Next mycell

I am trying to validate data in two columns in Excel. I have some existing code that compares the values of columns A & B of the worksheet and highlights the values that are not matching. What is in column A, but not in column B gets highlighted red in column A and what is in column B but not in A gets highlighted yellow. What I am faced with now is that there are certain values in column A that are equivalent to the values in column B, but they are not the same text. For example; cell A4 may have 'Question1' as the value and cell B16 may have 'Answer1' as the value. I need excel to understand this and not apply conditional formatting. Can someone please tell me what code will I need to add (perhaps another if statement?) to the existing code below to do this?

For Each mycell In wks.Range("A2:A" & lastRowA)

If mycell.Value <> "" Then

matchValue = Application.WorksheetFunct

If Err.Number <> 0 Then

mycell.Interior.Color = vbRed

Err.Clear

End If

End If

Next mycell

For Each mycell In wks.Range("B2:B" & lastRowB)

If mycell.Value <> "" Then

matchValue = Application.WorksheetFunct

If Err.Number <> 0 Then

mycell.Interior.Color = vbYellow

Err.Clear

End If

End If

Next mycell

8 Comments

So unless there is a very limited number of equivalent matches then what you are asking is not realistically feasible

Michael

Michael

Proposed solution:

I suggest perhaps a lookup table for comparisions of corollaries from A to B (or B to A) so those are also compared.

E.g.,

IFTHIS THENthisIStheSAME

Question1 Answer1

Answer1 Question1

Then you'd only need to add one more condition, in addition to the match.

Assuming that table was called COROLLARY (for demonstration purposes), here's your code:

```
Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet
Dim matchValue As Long
Dim lastRowA As Long, lastRowB As Long
Dim foundDup, foundCorly As Boolean
Set wkb = ActiveWorkbook
Set wks = wkb.ActiveSheet
lastRowA = wks.Range("A" & wks.Rows.Count).End(xlUp).Row
lastRowB = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
On Error Resume Next
For Each mycell In wks.Range("A1:A" & lastRowA)
foundDup = False
If mycell.Value <> "" Then 'if nonblank cell, then compare
matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("B1:B" & lastRowB), 0)
If Err.Number <> 0 Then 'didn't find an exact match
Err.Clear
matchValue = Application.WorksheetFunction.VLookup(mycell.Value, [COROLLARY], 2, 0)
If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
mycell.Interior.Color = vbRed
Err.Clear
End If
End If
End If
Next mycell
For Each mycell In wks.Range("B1:B" & lastRowB)
If mycell.Value <> "" Then 'if nonblank cell, then compare
matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("A1:A" & lastRowA), 0)
If Err.Number <> 0 Then 'didn't find an exact match
Err.Clear
matchValue = Application.WorksheetFunction.VLookup(mycell.Value, [COROLLARY], 2, 0)
If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
mycell.Interior.Color = vbRed
Err.Clear
End If
End If
End If
Next mycell
End Sub
```

See attached demo workbook:

inAnotB-r3.xls

For fun, I did a slight optimization, putting the work in a subroutine that's called by the main routine - as the two loops are doing the same work, just on the opposite columns.

Here's the final code:

```
Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = ActiveWorkbook
Set wks = wkb.ActiveSheet
Call markNoMatch(wks, "A", "B", 1, [COROLLARY], vbRed)
Call markNoMatch(wks, "B", "A", 1, [COROLLARY], vbRed)
End Sub
Sub markNoMatch(wks As Worksheet, colCompare As String, colCompare2 As String, startRow As Long, lookup_array As Range, lcolorMark As Long)
'routine searches colCompare2 with each element from colCompare. If a match is not found, then a final
'check lookup up against the table lookup_array is done, to see if there are corollary values that would also
'qualify as a match
'If a match is not found, the cell is marked with lColorMark interior fill color
Dim myCell As Range
Dim lastRow As Long
Dim lastRow2 As Long
Dim matchValue As Long
lastRow = wks.Range(colCompare & wks.Rows.Count).End(xlUp).Row
lastRow2 = wks.Range(colCompare2 & wks.Rows.Count).End(xlUp).Row
For Each myCell In wks.Range(colCompare & startRow, colCompare & lastRow)
If myCell.Value <> "" Then 'if nonblankcell, then compare
On Error Resume Next
matchValue = Application.WorksheetFunction.Match(myCell.Value, wks.Range(colCompare2 & startRow & ":" & colCompare2 & lastRow2), 0)
If Err.Number <> 0 Then 'didn't find an exact match
Err.Clear
matchValue = Application.WorksheetFunction.VLookup(myCell.Value, lookup_array, 2, 0)
If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
myCell.Interior.Color = lcolorMark
Err.Clear
End If
End If
End If
Next myCell
On Error GoTo 0
End Sub
```

See attached demo workbook.

Enjoy!

Dave

inAnotB-r4.xls

Please accept my apologies for not asking a related question. I tried your demo workbook and it still highlighted Answer1 and Question1 in the columns. Since those values were in the table, they should not have been highlighted, correct?

You are correct, my Vlookup returns a string and I was catching it in a long variable. That's repaired.

Here's the update:

Dave

inAnotB-r5.xls

Title | # Comments | Views | Activity |
---|---|---|---|

Vb script to restart the services | 13 | 58 | |

Show All Open Workbooks, Allow User To Select, Then Combined Those Selected | 17 | 35 | |

Create exported XLS from Query | 19 | 18 | |

DataSort absolute values? | 5 | 11 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!