userform to count cell colours and display in textboxes

Hey all,

I have a tracker 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 attached an example of the workbook:

Basically, I want to be able to select an employee from the combobox1 and it automatically calculate the number of holiday days, sick days or other days the employee has taken throughout the year by counting the colour filled cells..

I found a few code examples that do something similar but require cells to have formula's in them, I'd really like to keep this to the userform rather than cells on the worksheet. Can the following code (which i found on this site) be modified to suit my needs or will i need something more bespoke?

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

Any help is massively appreciated.

Adam

PS: I've asked this question on MrExcel too, i will keep them both updated with responses.

http://www.mrexcel.com/forum/showthr...08#post2666808

Add this to the form:

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