Best VBA option to compare multiple cells on multiple sheets and return results

[AuntSteph]

This is my first post and I haven't found what I need by researching, so I thought I would submit a post. I have a workbook with two spreadsheets. The first contains specific information about a medical claim (year of the claim (column "N", diagnosis codes columns ("R" -"AP"). On the second spreadsheet it lists all of the diagnosis codes and the year that it met specific criteria.

The second spreadsheet will have one row per diagnosis code per year for a total of 60800 rows. What I need to do is check the claim year on sheet 1 (Column "N" with the year on sheet 2 (Column "A") and if that matches, then I need to compare the dx on sheet 2 (column "E") with the diagnosis codes on sheet 1 ("R" - "AP"). If that diagnosis does not match, then I need to look at the next row on sheet 2 to see if that year matches the year on my claim. I need to do this for 65 rows on sheet 1.

If there is a match, I need to change the cell color on sheet 1 for the matched diagnosis code.

I don't know if I need a jagged array, a rectangular array, a simple series of if then statements. I am relatively new to VBA and I am totally in over my head.

Thank you in advance for your assistance and guidance!

 

[MickG]

No, you can run the code as you like, as long as its in the right workbook.:-
Your data need to be in the same columns as shown in the example for both sheets.
if you just replace "my data" with yours, you should get the required result.
Failing that you will need to send an example file with "Box.com"

 

[AuntSteph]

Well color me red and knock me over with a feather. Even though the year was listed in the same format on both sheets, it wasn't until I entered in the year again and copied and pasted what I typed in all appropriate cells that it matched. I only discovered that because I decided I would do a simple match/index to get the data the long way. I don't know WHY it wasn't recognizing the same format - I had even done a concatenate on the cells just because I am in the habit of doing that when using the match function. So excited it is working now. THANK YOU SO VERY MUCH!!!! I appreciate your patience and assistance!

Stephanie

 

[MickG]

Glad you now have it working.
Regrds Mick