• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Count by Color using VBA

Dear Expert:

Entering the following formula into the formula bar, the number of  cells in Column E that contain the color in E2 are counted

countbycolor(E2,E1:E15)

The user-defined worksheet function works fine but I would like to get the whole thing automated, i.e. ...

A macro is  ...
... to  write this formula into B7 of the worksheet named 'Results' of the active workbook
... and the counting of the shaded cells is to be effected in Column E of a sheet named 'Evaluation' and where E5 is the cell reference that contains the background color (to be counted).
... The range of cells can be any number, e.g. E1:465 or E1:3244, i.e the range is variable

It would be great if somebody could help me with that. Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
    If ICol = TCell.Interior.ColorIndex Then
        CountByColor = CountByColor + 1
    End If
Next TCell
End Function

Open in new window

0
Andreas Hermle
Asked:
Andreas Hermle
  • 3
  • 2
1 Solution
 
MacroShadowCommented:
Function CountByColor(CellColor As Range, CountRange As Range)
    Application.Volatile
    Dim ICol As Integer
    Dim TCell As Range
    ICol = CellColor.Interior.ColorIndex
    For Each TCell In .CountRange
        If ICol = TCell.Interior.ColorIndex Then
            CountByColor = CountByColor + 1
        End If
    Next TCell
End Function

Sub Demo()
    Dim rng As Range
    rng = Application.InputBox("Please enter the range to count.", "Demo", , , , , , 8)
    Sheets("Results").Range("B7") = CountByColor(Sheets("Evaluation").Range("E5"), Sheets("Evaluation").rng)
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi MacroShadow,  

thank you very much for your great and swift support.

I am afraid to tell you that the code is throwing an error code on line 15: Error Code 91.

I am doing some more testing.

Again, thank you very much. Regards, Andreas
0
 
MacroShadowCommented:
Sorry my bad. Either one of these will work.

Function CountByColor(CellColor As Range, CountRange As Range)
    Application.Volatile
    Dim ICol As Integer
    Dim TCell As Range
    ICol = CellColor.Interior.ColorIndex
    For Each TCell In CountRange
        If ICol = TCell.Interior.ColorIndex Then
            CountByColor = CountByColor + 1
        End If
    Next TCell
End Function

Sub Demo1()
    Dim strRng As String
    strRng = InputBox("Please enter the range to count.", "Demo")
    Sheets("Results").Range("B7") = CountByColor(Sheets("Evaluation").Range("E5"), Range(strRng))
End Sub

Sub Demo2()
    Dim rng As Range
    Set rng = Application.InputBox("Please enter the range to count.", "Demo", , , , , , 8)
    Sheets("Results").Range("B7") = CountByColor(Sheets("Evaluation").Range("E5"), rng)
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
ok, macro shadow, great.  This did the  trick. Thank you very much for your great and professional support. I really appreciate it. Regards, Andreas
0
 
MacroShadowCommented:
Glad to help:)
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now