asked on

Excel Comparison Macro w/IF Statement

Hello,

The attached sample has a macro that runs a comparison between two worksheets based on ID and uses conditional formatting to note any non-matches. I'm hoping someone can assist by editing the code so that it only identifies this mismatch if Reason 2 or Reason 3 appears in column G on File_A. So on the attached example, once this change is made, row 6 would not be identified as a non-match because it shows Reason 1 in column G.

Thanks!
EE-Sample-Comparison-Only.xlsm

Martin Liss

Give this a try.
29155986.xlsm

Ron Bayes

ASKER

Apologies - situation that when you start describing something it makes perfect sense until you reread it after reviewing results. What I meant to ask is rather than edit the code entirely I just need this task added to it. So the mismatch/conditional formatting function is only getting applied to those specific reasons (Reason 2 or Reason 3). All other reasons would be ignored.

Again - sorry for the confusion.

Martin Liss

I'm sorry but I don't understand. Can you show me what the output should look like?

Martin Liss

Are you still interested in pursuing this?

Ron Bayes

ASKER

Hello - apologies for the delay on getting back with you. I've been out of office (Labor Day vacation) and just catching up.

I've attached a file which shows how I'm expecting to see the output. The code should identify an ID match between file A column C and file B column U - - but only if Reason 2 or Reason 3 are listed in column G.

Even though there are ID matches on rows 15-17 it would not highlight since the target reasons are not listed.
EE-Sample-V3.xlsm

Martin Liss

I'm not particularly proud of this solution because I don't know enough about formulas to be able to modify the existing conditional formatting formulas so that they would do what you want, but my solution which uses straight VBA seems to work.
29155986a.xlsm

Ron Bayes

ASKER

I believe that will work. So if I want to add additional criteria, for example, want to also highlight Reason 4 and Reason 5, would I modify the code per below?

.Columns("G:G").AutoFilter
.Range("$G$1:$G$" & .Cells(Rows.Count, "G").End(xlUp).Row).AutoFilter Field:=1, _
Criteria1:="=Reason 2", Operator:=xlOr, Criteria2:="=Reason 3", Operator:=xlOr, Criteria3:="=Reason 4", Operator:=xlOr, Criteria4:="=Reason 5"

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Bayes

ASKER

Appreciate all your help on this one. Thanks!

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
       Experts Exchange Top Expert VBA 2018
       Experts Exchange Distinguished Expert in Excel 2018