CODEWITHSUNDEEP
excelvbavlookupcountif
elia
elia

Reputation: 91

Vlookup and Countif by its color and lookup value

I am trying to make a risk map. I am trying to find the number of cells with the corresponding colour and lookup value. For example, E3 will be 2 because, on the table next to it, there are 2 red highlighted risks for credit risk. the picture is only example, I am trying to count it automatically because there are more than a hundred risk data which is updated every month so I don't want to count every time. I tried to find a VBA function and tried to combine excel's own function but couldn't do it.

Upvotes: 0

Views: 1272

Answers (1)

karma
karma

Reputation: 2009

If I understand you correctly :

Sub test()
Dim id1 As Long: Dim id2 As Long: Dim id3 As Long
Dim cnt1 As Long: Dim cnt2 As Long: Dim cnt3 As Long
Dim unik As Range: Dim rg As Range: Dim cell As Range: Dim co As Range

Set unik = Range("D3:D7") 'change if needed
Set rg = Range("J2:J16") 'change if needed
id1 = Range("e2").Interior.ColorIndex 'change if needed
id2 = Range("f2").Interior.ColorIndex 'change if needed
id3 = Range("g2").Interior.ColorIndex 'change if needed
cnt1 = 0: cnt2 = 0: cnt3 = 0

For Each cell In unik
    With rg
        .Replace cell.Value, True, xlWhole, , False, , False, False
        For Each co In .SpecialCells(xlConstants, xlLogical).Offset(0, 1)
            If co.Interior.ColorIndex = id1 Then cnt1 = cnt1 + 1: cell.Offset(0, 1).Value = cnt1
            If co.Interior.ColorIndex = id2 Then cnt2 = cnt2 + 1: cell.Offset(0, 2).Value = cnt2
            If co.Interior.ColorIndex = id3 Then cnt3 = cnt3 + 1: cell.Offset(0, 3).Value = cnt3
        Next
        .Replace True, cell.Value, xlWhole, , False, , False, False
    End With
    cnt1 = 0: cnt2 = 0: cnt3 = 0
Next

End Sub

Based on your image, what the sub do:
Have a risk range into variable unik (D3:D7)
Have the data range in J2:J16 into variable rg
Get the color id on each cell of E2:G2 as variable id1 to id3
Put beginning value of zero into variable cnt1 to cnt3.

Then it loop to each cell in unik
replace the value in rg which has the looped cell value with logical TRUE
Then it loop to the cell in rg (as variable co) which value is TRUE, then check if the co.offset(0,1) color index = id1 then add 1 to cnt1 and put the cnt1 value into cell.offset(0,1)... and so on ---> (sorry it's very difficult for me to explain it in English). Then put back the cell.value, replacing the TRUE value.

Please note that the sub assumed each cell value in J2:J16 (rg variable) is exactly the same with any cell value in D3:D7 (unik variable)

Upvotes: 2

Related Questions