Userform Textboxs to count cells with a specific coloured backgrounds

[AGrace]

Morning all,

I have a holiday planner (that is functioning nicely thanks to help from MickG) that displays employee holiday/sickness absence throughout the year (indicated by specific colours).

I have a userform that I want to count the number of these specific colours per employee.

I have a userform that looks like this:

When the user selects an employee from the combobox (all comboboxes, labels etc have default names), the textboxes will count the specific colours for each absence type filled out for that employee.

The colour codes used are :

    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37

The range for the count is column C to column NC, and row's 6 through to 20 (but then the row should be selected by choosing the employee from the drop down).

What I'm stuck with is how to count the colours in the cells for each employee. Any help is appreciated!

Thanks,

Adam

 

[AGrace]

FYI - I've duplicated this question on ExcelForums, as I wanted to increase my chances of help. I will keep both updated with responses.

http://www.excelforum.com/excel-programming/770538-userform-to-count-cell-colours-and-display-in-textboxes.html#post2500185

 

[AGrace]

Sorted with this.

Private Sub ComboBox1_Change()
    Dim lngRow As Long
    Dim rngData As Range
    If Me.ComboBox1.ListIndex <> -1 Then
        ' get row number - employee range starts in row 6
        lngRow = 6 + Me.ComboBox1.ListIndex
        With Sheet1
            Set rngData = .Range(.Cells(lngRow, "C"), .Cells(lngRow, "NC"))
        End With
        Me.TextBox1.Value = CountByColor(rngData, Sheet1.Range("B22")) ' holiday
        Me.TextBox2.Value = CountByColor(rngData, Sheet1.Range("B23")) ' sick leave
        Me.TextBox3.Value = CountByColor(rngData, Sheet1.Range("B24")) ' other
    End If
End Sub
Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function

credit to romperstomper from ExcelForums!