VBA: Changing Interior Color of Dictionary Array Item

[mykulpasskwa]

Hello, I have set up a dictionary that looks at term years and individually adds each term with matching years. I am trying to highlight the array item that goes over a limit, but I can't figure out how to reference the array item to the cell range it's associated with. I haven't worked with dictionaries before a couple days ago. Normally I would use something like ws.Range("F" & i).Interior.Color = 15652797 but that clearly doesn't work in this case.

In this case the terms all start with 2019 and the limit is 15, the the sum of 201910 and 201930 meets or exceeds that limit so I want to set the color to yellow. I have it working where arr(1) is 201910, arr(2) is 201930, and arr(3) is 201950. So if arr(2) is the item that equals or exceeds the limit, how can I reference that item so I can change the color of the offset value?

The code is much larger than this, but this is the relevant part I think:

        If Year1 = Year2 Then 
            termsubtotal = arr(i, 5) + arr(i + 1, 5)
            If termsubtotal < benefitresult Then 
                    'Changes array item to Blue (Interior color = 15652797)
            Else
                If termsubtotal >= benefitresult Then
                    'Changes array item to Yellow (Interior color = 65535)
            Else
                If Year1 <> Year2 Then
                    termsubtotal = termsubtotal + arr(i, 5)
                End If
            End If

I'm willing to provide more if requested, but figured I'd get rid of some irrelevant things.

 

[mykulpasskwa]

I show you another version using a dictionary

I didn't see the dictionary one the first time around. I used the first one you gave and that worked perfectly. I'll run the dictionary one later today and see which works better. I just got tasked with something else I have to attend to. Thank you so much though!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[mykulpasskwa]

Glad we could help & thanks for the feedback

Yeah, they both work without any issues. I like the first one better for our purposes just because it can alternate between yellow and blue. I could manage to have the second script do that with what you've already provided, but it's not worth fixing what isn't broken. Ya know, more than just using your code it helped me understand how the logic needs to work and where the pieces need to go, so I really appreciate the time and effort you put in. Had I thought to use And dic(nYear) = Empty before I probably could have saved us both the trouble but this is how we learn. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.