• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

color cell count in a row

R

rajkumar.shukla

New Member
I've a exel file in which we create a leave plan for jan to dec in different sheet.

I fill color on a cell which shows leave taken. Now the problem is that I'm not able to calculate(sum) those colored cell. Plz help me and provide a macro or VBA code to sum those cells(in row).
 
Here's a basic UDF that will help you out. Copy it into a regular module in the VBE.

Code: 
Function SumColors(CriteriaCell As Range, SumCells As Range)
Dim MyColor As Integer
Dim c As Range
MyColor = CriteriaCell.Interior.ColorIndex

For Each c In SumCells
   If c.Interior.ColorIndex = MyColor Then
      SumColors = SumColors + c.Value
   End If
Next c
End Function
Then, in your worksheet, you can use this formula. Function has 2 arguements. The first should be a single cell that has the color you want to sum by. The second is the range of cells you want to look at. So, if you want to sum the cells in A2:A10 that are red, and B10 is currently red, formula would be:

=SumColors(B10,A2:A10)


NOTE: Function does not work based on Conditional Formats, only regular cell formats.
 
Last edited:
Hi Shukla ,


If your requirement is only to find out how many cells within a range are coloured , then a slight change to Luke's macro will do the job. Try this :

Code: 
Function SumColors(SumCells As Range)
Dim NoColor As Integer
Dim c As Range
NoColor = -4142
For Each c In SumCells
If c.Interior.ColorIndex <> NoColor Then
SumColors = SumColors + 1
End If
Next
End Function
If you copy this macro , go into your VBA editor , insert a module , and paste this macro , then in any unused cell , you can put this into a formula , such as for example :


=sumcolors(C15:K15)


If any cells in the range C15:K15 are coloured , the formula will return the number of coloured cells.


Narayan
 
Last edited by a moderator:
Rather than filling the cells why don't you use abbreviation for different leave types ? Like


SL for sick leave

PL for Personal leave etc.


and later you can easliy sum the leaves using countif formula.


The drawback of these UDF is you have to calculate(F9) the workbook to get your total once you change the color of the cell.


Kris
 
Adding a simple statement Application.Volatile to the code will make the function volatile.

eg:

[pre]
Code: 
Function SumColors(SumCells As Range)
Application.Volatile
Dim NoColor As Integer
Dim c As Range

NoColor = -4142
For Each c In SumCells
If c.Interior.ColorIndex <> NoColor Then SumColors = SumColors + 1
Next
End Function
[/pre]
 
Hi Shukla ,


For someone who should be interested in getting help to resolve your problem , you seem to be communicating as if you are judging our answers !


If you want to get your problem resolved please do the following :


1. Let everyone know to whom you are communicating ; since your original post , 4 members of this forum have responded ; "still not working" refers to whose response ?


2. If you go to a doctor and tell him / her you are not well , is that enough information for the doctor to diagnose your problem , and prescribe the correct medicine ? The more information you give , the faster you will get a solution.


3. If you are genuinely ill-at-ease communicating in English , the easiest way to get your problem resolved is to upload your workbook , with some sample data. Can you do that ?


Narayan
 
You must log in or register to reply here.
Back
Top