Validate data between two columns

Question

Hi,

Is there any macro help to highlight if the value in column not matching with the column next to it.

Example:

Cell A1 has the name "John" and "B1" is the employee ID: 23457. Cell "A5" has Peter and Employee ID 23985 in "B5" In this case if the same name appears anywhere in the same column with different employee ID next to it, can the macro highlight the incorrect rows please?

Answer 1

Select A1:A100 (or as far down as necessary). A1 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=OR(ISNUMBER(FIND("™", A1)), ISNUMBER(FIND("©", A1)), ISNUMBER(FIND("®", A1)))

Click Format...

Activate the Fill tab.

Select a fill color.

Click OK, then click OK again.

Answer 2

Select A1:B100 (or as far down as necessary). A1 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIFS($A$1:$A$100, $A1, $B$1:$B$100, "<>"&$B1)

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

Answer 3

Hi HansV,

Thankyou so much, this works great👍

Answer 4

Also, is there anyway I can find special character like trade mark symbols appears in column A?