How to sum cells filled with colour?

[arapone]

How is it possible to Count the number of cells which are filled with any color in a particular row using excel 2010?

I am not too familiar with VBA, so if i need to use it, please help me out with some instructions.

Thanks in advance.

 

[VoG]

If they are manually colored try this http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

 

[arapone]

Yes they are maunally colored, however the link only shows to count based on a colour reference to a specific cell. I am looking to count all colours together (sum of all cells in a row which are colored. Note the row has various colours).

Also i note that with VBA the function will not auto update if the contents are changed, unless the formula is re-entered or chanted. is there a possibility of doing this outside of VBA?

 

[VoG]

Try this: press F9 to update

Function CountColor(r As Range) As Long
Application.Volatile
Dim c As Range
For Each c In r
    If c.Interior.ColorIndex <> xlNone Then CountColor = CountColor + 1
Next c
End Function

 

[arapone]

Try this: press F9 to update

Function CountColor(r As Range) As Long
Application.Volatile
Dim c As Range
For Each c In r
    If c.Interior.ColorIndex <> xlNone Then CountColor = CountColor + 1
Next c
End Function

This works perfect. Thanks!

 

[arapone]

I have another question,

How is it possible to use this function to create a sum at the bottom of each columm, however I want each true count to be multiplied by the number in column D for that particular row.

<a href="http://www.flickr.com/photos/23463579@N07/5529490481/" title="excel doc by fouselli, on Flickr"><img src="http://farm6.static.flickr.com/5059/5529490481_9645779f2e.jpg" width="399" height="306" alt="excel doc" /></a>

For example, the sum of Column E should return 30 and the sum of column J is 24, Sum of column K is 29, etc.

 

[arapone]

Any one?

 

[arapone]

I have a formula which checks a colum everytime a cell is highlighted and produces a sum of the total number of highlighted cells in the column.

=IF(CountColour(AC9:AC84)>0,CountColour(AC9:AC84),"")


What I would like the formula to do is everytime a cell is highlighted, take that cell and multiply it by a number in column A. The sum of all these instances should be displayed.

I.e. (x denotes a highlighted cell)

AC9 = 5 .... B9 = x
AC10 = 6 .... B10 =
AC11 = 2 .... B11 = x

Result 7


Any suggestions?

 

[_BradW]

I have applied the color function into VBA, but it isn't recognizing conditional fill coloring of the cells in question.... what can be done to remedy this?