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

Unfortunately code can only do what it is told. To do what you are asking you would have anticipate all possible scenarios where the cells are equivalent and then place conditions into the code for each scenario.

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

Michael

0

daintysallyAuthor Commented:

There are 10 possible scenarios. Can you tell me how to incorporate the conditions in the code?

Could you please post an example file and the possible scenarios and I will put something together

Michael

0

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 WorkbookDim wks As WorksheetDim matchValue As LongDim lastRowA As Long, lastRowB As LongDim 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 mycellEnd Sub

I had a couple variables in the above posting, I didn't end up using, so you can remove lines 6 & 17.

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 WorkbookDim 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 SubSub 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 colorDim myCell As RangeDim lastRow As LongDim lastRow2 As LongDim 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 0End Sub

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?

@daintysally - no need for apologies! This was just for future, so your questions get a better chance by having the benefit of prior dialogue/spreadsheets, etc., No worries at all! I should have said it like "gentle reminder: please ask related question..."

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

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Michael