Solved

Count by Color using VBA

Posted on 2014-02-15
5
375 Views
Last Modified: 2014-02-16
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
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39861981
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
 

Author Comment

by:AndreasHermle
ID: 39862090
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
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39862254
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
 

Author Closing Comment

by:AndreasHermle
ID: 39862388
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39862390
Glad to help:)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question