Count colourindex - VBA

[wrightyrx7]

Hi all,

I have some code i tried to put together but it doesnt seem to be working.

Can anyone see where im going wrong? Because when the colorindex IS equal to the values its not going to THEN

Sub GenerateSick()
Dim Sick, Sickness As Worksheet
Dim g, r, a As Integer
Dim lRow As Long
Dim Rng, i As Range

Set Sick = Sheets("Sick")
Set Sickness = Sheets("Sickness (Confidential)")

    g = 0
    r = 0
    a = 0
lRow = Sickness.Cells(Rows.Count, 13).End(xlUp).Row
Set Rng = Sickness.Range("M7:M" & lRow)
    For Each i In Rng
        If i.Interior.ColorIndex = 4 Then g = g + 1
        If i.Interior.ColorIndex = 3 Then r = r + 1
        If i.Interior.ColorIndex = 45 Then a = a + 1
    Next i

Sick.Range("B5").Value = g
Sick.Range("B6").Value = a
Sick.Range("B7").Value = r


End Sub

[JOHN H. DAVIS]

Maybe:

Sub wrightyrx7()
Dim Sick, Sickness As Worksheet
Dim g, r, a As Long
Dim i As Long

Set Sick = Sheets("Sick")
Set Sickness = Sheets("Sickness (Confidential)")

    g = 0
    r = 0
    a = 0
Sickness.Activate
For i = Range("M" & Rows.count).End(3)(1).Row To 7 Step -1
        If Range("M" & i).Interior.ColorIndex = 4 Then g = g + 1
        If Range("M" & i).Interior.ColorIndex = 3 Then r = r + 1
        If Range("M" & i).Interior.ColorIndex = 45 Then a = a + 1
Next i

Sick.Range("B5").Value = g
Sick.Range("B6").Value = a
Sick.Range("B7").Value = r
End Sub

[satabp]

Hi,

The code is working fine..

It is not going to "Then" because the condition was not satisfied... which means ColorIndex is not in 4, 3 and 45.

[jmac1947]

Hi wrightyrx7,
is it not going to the "then" while stepping through with F8 or because the final assignments are not working??

I a have seen experts on this forum differ on the results of your line

Dim Sick, Sickness As Worksheet

in terms of what "Sick" is defined as.

I haven't tried to execute the code as I don't have a sample workbook and am happy for experts to suggest the right solution so I can learn some better practices as well

Jmac1947

[wrightyrx7]

Hi guys,

I have found its was a problem with the data that was being copied over.

The colours were not in my colour palette.

Added them and now it works fine.

Thanks for looking at this for me.