Automatic Cell Count If Background Color is a Certain Color in a Cell

[Woutie]

Hi all,

I have a spreadsheet that I seriously need help with automating it PLEASE!

Basically I am running a call center and this sheet is for tracking the agents who are on a call, who are available/free and who might be reserved for a upcoming issue e.g. product recall announcement.
In ROW 29-31 for each module I would like the sheet to update the amount of each module based on the particular background color I change during the run of the day.
I have to keep the description (E.G. A1-3T or B2-4S) in each cell as it is in the sheet, since this links the particular agent.

I was told this can be done with VBA, but I do not have any idea as to how to do this unfortunately

My BOX.COM link to the spreadsheet -> https://app.box.com/s/i4ithcqezj9099f74nstl6sga2c1bg3w

Woutie

 

[Fluff]

Hi & welcome to MrExcel
How about

Function CountColour(rng As Range, Clrng As Range)
   Dim cl As Range
   Application.Volatile
   For Each cl In rng
      If cl.Interior.Color = Clrng.Interior.Color Then
      CountColour = CountColour + 1
      End If
   Next cl
End Function

And used like

Excel 2013/2016
	A	B	C	D
29	Total calls in progress	14	14	12
30	Total new issues logged	0	0	0
31	Possible/reserved Issues calls	0	0	0
Network Power Audit

Cell Formulas
Range	Formula
B29	=CountColour(B$4:B$27,$A29)
B30	=CountColour(B$4:B$27,$A30)
B31	=CountColour(B$4:B$27,$A31)
C29	=CountColour(C$4:C$27,$A29)
C30	=CountColour(C$4:C$27,$A30)
C31	=CountColour(C$4:C$27,$A31)
D29	=CountColour(D$4:D$27,$A29)
D30	=CountColour(D$4:D$27,$A30)
D31	=CountColour(D$4:D$27,$A31)

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-certain-color-automatically.html#post4963290

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Woutie]

Hi MrExcel,

Understood and thank you for your prompt reply!

Woutie

 

[Woutie]

Hi there,

I have tried this code, it works but I have to refresh "Calculate Now" each time after changing a different cell's back ground color. Can this be made that it will be automatically count the color change once it has been change and I click/select the next cell?

Woutie

 

[Rick Rothstein]

I have tried this code, it works but I have to refresh "Calculate Now" each time after changing a different cell's back ground color. Can this be made that it will be automatically count the color change once it has been change and I click/select the next cell?

Unfortunately, there is nothing that can be done about that... the changing of the color of a cell cannot be "seen" by VBA code. As to your follow up question, we can create a SelectionChange event procedure that will update the colors when the user changes the selection to a different cell on the same worksheet...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim R As Long, C As Long, Blue As Long, Purple As Long, Gold As Long
  For C = 2 To 24
    Blue = 0
    Purple = 0
    Gold = 0
    If Len(Cells(4, C).Value) Then
      For R = 4 To 27
        Select Case Cells(R, C).Interior.Color
          Case 15773696: Blue = Blue + 1
          Case 10498160: Purple = Purple + 1
          Case 49407:    Gold = Gold + 1
        End Select
      Next
      Cells(29, C).Value = Blue
      Cells(30, C).Value = Purple
      Cells(31, C).Value = Gold
    End If
  Next
End Sub

This code is a little wasteful in that it executes as soon as a different cell is selected even if no colors have been changed; however, the number of cells you are monitoring is small enough that you should not see the effects of this wastefulness.


HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Woutie]

Hi Rick,

Thank you for your reply!
I managed to get the code in (no idea what it does or how it works ) but I am not sure what formulas I need to use the spreadsheet now to sum or color the colored cells?
Please hold the hand of a Novice Excel user...

Woutie