Add together Values in Coloured Cells

[jamieleeuk]

Hi,

I have had a look through some searches and I can't find any that have answered this question before so would appreciate it if someone could assist.

I have a few cells on a sheet which will be coloured red. They won't always be the same cell references, but they will always be the same colour.

I was wondering if there was some VBA code somewhere which would allow me to put in cell D1 the total amount of the coloured cells.

Please, if you need more info, just ask'

Kind Regards
Jamie

 

[Biz]

Try Link below

http://www.ozgrid.com/VBA/Sum.htm

 

[Sandeep Warrier]

Use this code in a module.

Function CountColour(CellRng As Range, CellColInd As Range)

    Dim i As Double
    Dim ColInd As Double
    Dim rCell As Range
     
    Application.Volatile
    
    i = 0
    
    ColInd = CellColInd.Interior.ColorIndex
    
    For Each rCell In CellRng
    
        If rCell.Interior.ColorIndex = ColInd Then
        
            i = i + 1
            
        End If
    
    Next rCell
    
    CountColour = i
    
End Function

Use it as follows:

=CountColour(YourDataRange,ReferenceOfTheColouredCell)


for example, if your data is in A1:A100 and A1 is red then

=CountColour(A1:A100,A1)

This has the added advantage in that you can use it to count not just red cells, but any coloured cells.

Hope this helps.

 

[jamieleeuk]

many thanks for both replies.