Macro for highlighting unmatched records on another sheet

[paulcherianc]

Can you please see the attachment for more info. I need a macro that highlights unmatched records from another sheet.

 

[YasserKhalil]

Hello
In the sheet named "ADJ FORMAT" select the range B10 to P11 then go to Home tab and Conditional Formatting and 'New Rule' >> Select 'Use a formula to determine .....' and type that formula

=NOT(ISNUMBER(MATCH($P10,CHECK!$D:$D,0)))

Then click 'Format' and do the formatting task as you wish (Fill tab for example and select any desired color) and finally click 'OK'

 

[p45cal]

paulcherianc, I see you've created a Check column on both of these sheets. If they're only for this highlighting, you can ditch them and let the conditional format formula handle that with:

=NOT(ISNUMBER(MATCH($G10 & $B10,CHECK!$B$2:$B$6 & CHECK!$C$2:$C$6,0)))

ps. does it have to be a macro?

 

[paulcherianc]

Hello
In the sheet named "ADJ FORMAT" select the range B10 to P11 then go to Home tab and Conditional Formatting and 'New Rule' >> Select 'Use a formula to determine .....' and type that formula

=NOT(ISNUMBER(MATCH($P10,CHECK!$D:$D,0)))

Then click 'Format' and do the formatting task as you wish (Fill tab for example and select any desired color) and finally click 'OK'

Hi,

How can I add if "NOT BLANK" to this formula. If P10 is blank conditional format should not work. I have uploaded the sample file.

 

[p45cal]

I'll leave YasserKhalil to answer for his version of the solution.
For mine:

=NOT(OR(ISNUMBER(MATCH($G10&$B10,CHECK!$B$2:$B$6&CHECK!$C$2:$C$6,0)),ISBLANK(B10)))

 

[paulcherianc]

I could get the result through the following formula:

=IF($P10<>"",NOT(ISNUMBER(MATCH($P10,'Check'!$D:$D,0))))

Thanks!