Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • 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
AndreasHermle
Asked:
AndreasHermle
  • 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
 
AndreasHermleAuthor 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
 
AndreasHermleAuthor 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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