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!

 

[AuntSteph]

[/IMG]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 "B" on the example, diagnosis codes - columns "C" -"AA" on the example. 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 "B" 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 ("C" - "AA"). 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!

I thought it might help if I attached a spreadsheet to make it easier to follow the question. Thanks so much!

[/IMG]

 

[MickG]

You may get a better response if you post an example of your file, showing both sheets and expected results, Using "Box.com" (free file sharing)

 

[AuntSteph]

Sheet 1
N R S T U
Year Diag 1 Diag 2 Diag 3 Diag 4
2009 4660 3314 3569
2010 5070 389 99591 49121
2014 4280 5849 51883 5119
2010 41070 51881 3314 3051




Sheet 2
A C E
2009 CC 19180
2009 CC 19181
2009 CC 19618
2010 MCC 11049
2010 CC 3314


I couldn't get the images to attach, so I have included this make-shift information. What I need to happen is for the macro to review the year in column "N" on sheet one with the year in column "A" on sheet 2. If the years match, then I want to match the codes in column "E" on sheet 2 with the codes in columns "R" - "AC" on sheet 1. For example, on line 4, 3314 is a match since it is 2010, however, code 3314 on row 1 is not a match because they are different years. I hope this helps explain my situation. Thanks so much!

 

[MickG]

What do you want to do when a match is found, (something to do with Colour )????
Not quite sure what this means :-"If there is a match, I need to change the cell color on sheet 1 for the matched diagnosis code. "
Please explain.

 

[AuntSteph]

Here is my failed attempt at a macro. It isn't an array, as I am at a loss with how to accomplish that successfully. So, I thought a series of loops and if statements might work, but it doesn't. This is what I have so far.

Sub MCC_codes()

Dim x As Integer
Dim y As Integer
Dim done As Integer
Dim z As Integer



Sheets("Sheet 1").Select
Range("N2").Select 'year

x = 0
y = 0
z = 4
done = 0

Do While done < 5

If ActiveCell.Offset(x, 0).Value = "" Then ' End of rows
done = done + 1

ElseIf ActiveCell.Offset(x, 0).Value = Worksheets("Sheet 2").Range("A" & y).Value Then 'compare year

If Worksheets("Sheet 2").Range("E" & y).Value = ActiveCell.Offset(x, z).Value Then 'same year, compare dx

ActiveCell.Offset(x, z).Interior.Color = 5296274 'make match dx green

ElseIf Worksheets("Sheet 2").Range("E" & y).Value <> ActiveCell.Offset(x, z).Value Then 'move to next dx code on claim

Do While z < 25 'loop through all dx columns
z = z + 1

If Worksheets("Sheet 2").Range("E" & y).Value = ActiveCell.Offset(x, z).Value Then
ActiveCell.Offset(x, z).Interior.Color = 5296274 'make match dx green

End If


Loop ' do until loop
z = 4
x = x + 1
y = y + 1

End If

End If



Loop
End Sub

 

[MickG]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG28Mar46
[COLOR=Navy]Dim[/COLOR] Rng1 [COLOR=Navy]As[/COLOR] Range, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng2 [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=Navy]Set[/COLOR] Rng1 = .Range(.Range("N2"), .Range("n" & Rows.Count).End(xlUp))
    Rng1.Offset(, 4).Resize(, 15).Interior.ColorIndex = xlNone
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] Sheets("Sheet2")
    [COLOR=Navy]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng2: .Item(Dn.Value & Dn.Offset(, 4).Value) = Empty: [COLOR=Navy]Next[/COLOR]
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng1
        [COLOR=Navy]For[/COLOR] Ac = 4 To 15
            [COLOR=Navy]If[/COLOR] .exists(Dn.Value & Dn.Offset(, Ac).Value) And Not Dn.Offset(, Ac).Value = vbNullString [COLOR=Navy]Then[/COLOR]
                Dn.Offset(, Ac).Interior.ColorIndex = 4
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]Next[/COLOR] Ac
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] With
MsgBox "Run"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[AuntSteph]

Thank you for your assistance. It still isn't working for me. When I go through the debug step it looks like it is only reviewing the first row on Sheet 1, however, even with that row, I know there are 2 cells that should highlight green and they are not doing that when I run. The macro works all the way through to the end and I see the message box, but it doesn't seem to be comparing the cell contents all the way through. Any additional help would be greatly appreciated!

With Thanks,
Stephanie

 

[MickG]

Have a look at this example:-
https://app.box.com/s/tdtx1no6j6nt05s8uixjlmaz31wk5krr

 

[AuntSteph]

This is EXACTLY what I want. I can't imagine what is wrong...Do I need to be in a specific cell when I run the macro? Thanks!