Do we have an Excel wizes here?

[CJ2008]

I need a formula that will count the # of cells or rows that are filled in with color.

Can anybody help?

 

[Shoopy]

If I remember correctly, in order to count a cell with color you need to build a VBA script which I think is difficult. Do these cells have a certain condition? It may be easier to build a countif function rather than build a script. You can check out MREXCEL.con and that should have guidelines on building scripts as my knowledge with them are very limited.

 

[CJ2008]

Fiery - thank you so much for responding.

The only condition is that they''re shaded/filled in with color. So we don''t need to count say 3 orange, 2 yellow, but 5.

Does that help? Is there an countif shading/color?

 

[Karl_K]

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

 

[CJ2008]

Karl,

It worked! You''re a genius!

Should the script work (or could we make it work) if we wanted to count more than one type of color and get a total overall - or do we need to do the formula for each color and then do a sum?

 

[Karl_K]

Date: 10/17/2009 12:27:24 PM
Author: CJ2008
Karl,


It worked! You''re a genius!


Should the script work (or could we make it work) if we wanted to count more than one type of color and get a total overall - or do we need to do the formula for each color and then do a sum?

You can call the script as many times as you need to inside a sum statement or just have a cell for each color and add the cells as needed.
It depends on what you need.

 

[CJ2008]

Well, I was hoping that say cell C1 is one color, and cell F2 is a different color - I would like to write ONE formula that will count both of them. But when I tried it it gave me an error - I''m sure the error is with how I put C1 and F2 into the formula - I had just a comma in between them...should it be C1+F2 (I know I''m leaving out the $, etc., just trying to understand how the cells should be listed for the formula to count them both.

 

[Karl_K]

rewrite to accept 2 colors... I haven't tested it but this should work,, and yes it is not well written but im not going to take the time to fix it

Function ColorFunction(rColor As Range, rColor1 As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

Dim 1Col1 as long

''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums or counts cells based on a specified fill color.

''''''''''



lCol = rColor.Interior.ColorIndex
1Col1= rColor1.Interior.ColorIndex


If SUM = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell,vResult)

End If

If rCell.Interior.ColorIndex = lCol1 Then

vResult = WorksheetFunction.SUM(rCell,vResult)

End If


Next rCell

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

If rCell.Interior.ColorIndex = lCol1 Then

vResult = 1 + vResult
End If



Next rCell

End If



ColorFunction = vResult

End Function

 

[CJ2008]

Thank you so much Karl.

I have NO idea and I mean NONE whether this script is proper code or not - all I know is that it solved my issue - so I think it''s awesome.

One more question - if I get out of Excel and then I want to VIEW that module/script (or edit it) how do I go about finding it? I couldn''t figure it out...

 

[Karl_K]

Date: 10/17/2009 2:20:53 PM
Author: CJ2008
Thank you so much Karl.


I have NO idea and I mean NONE whether this script is proper code or not - all I know is that it solved my issue - so I think it''s awesome.

One more question - if I get out of Excel and then I want to VIEW that module/script (or edit it) how do I go about finding it? I couldn''t figure it out...

Tools...Macro...Visual Basic Editor or just simply press the [Alt] and [F11]

 

[Shoopy]

Glad Karl was able to help you with the script!