Solved

# Substitue Values in Excel

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