Counting cells colored by conditional formating

[Safety Bill]

I'm looking for a way to count the cells in a row, that are conditionally formatted 4 different colors. Unfortunately, the 4 colors that I'm using aren't the standard Red, Green, Blue and Yellow. I have to use more pale versions.

Also, each row contains cells that are dates, text, numbers or formulas.

All I want to do is count how many cells of each color there are and have it show in a separate cell of that row (C4).

My final "Christmas Wish" is to be able to take this count and maybe use an IF formula of some type to conditionally format the cells Column C.

Here is the result I would like to achieve:

If D4:AF4 contains 1 or more pale red cells, put the count in C4 and stop.
If D4:AF4 contains 0 pale red cells, put count in C4 and proceed to next step.
If D4:AF4 contains 1 or more pale yellow cells, put the count in C4 and stop.
If D4:AF4 contains 0 pale yellow cells, put the count in C4 and proceed to next step.
And so on for the pale green and pale blues.

I've tried all the different VBA codes that I found when searching for this on the web, but to no avail. I either get an error message or 0. Please help before the safety man loses it.

 

[struass1]

I can't seem to get your code to work. It keeps appearing with compile error code saying expected end. Mine is not conditionally formatted

Do you know how to use custom functions?

Paste the following into a VBA module:

Function ColAC(TestCell As Range, TargetRange As Range, Optional SUM As Boolean)
 
    'Application.Volatile
 
    Dim TargetCell As Range
    Dim CellColor As Long
    Dim Result As Variant
 
    CellColor = TestCell.Interior.ColorIndex
 
    If SUM = True Then
        For Each TargetCell In TargetRange
            If WorksheetFunction.IsNumber(TargetCell) Then
                If TestCell.EntireRow.Hidden = False Then
                    If TargetCell.Interior.ColorIndex = CellColor Then
                        Result = Result + TargetCell.Value
                    End If
                End If
            End If
        Next
    Else
        For Each TargetCell In TargetRange
            If TestCell.EntireRow.Hidden = False Then
                If TargetCell.Interior.ColorIndex = CellColor Then
                    Result = Result + 1
                End If
            End If
        Next
    End If
    ColAC = Result
End Function

In your worksheet, you could (say) have 15 cells A1:A15, each formatted with one of your background colours. So, say in A1, you wanted to count all the cells in the range (A20:A100) whose colour matched A1's, you would have the formula
=COLAC(A1,A20:A100,true) if you wanted to SUM the values in A20:A100 with a matching colour

or

=COLAC(A1,A20:A100,false) if you wanted to COUNT the number of cells in A20:A100 with a matching colour.

You then copy this formula into your other 14 cells (making sure that as you drag down, the range of cells that you're testing against remains constant - use dollar signs where necessary.

Your test cell doesn't have to be the cell in which the formula goes either. In A1, you could have

=COLAC(B25,A20:A100,false)

if you wanted to use B25 as your colour matching cell.

Hope this helps

Pete


Does this make sense?

 

[GODDERS21X]

Hi there,

I am having the same problems and wanted some advice please. Basically I have made map in excel of a country using color blocking (took agues) and then used cells to represent an asset (there are nearly 500!) Each asset is conditionally formatted to change color (red,amber,green) depending on the data inputeed, Each cell has its own conditional formatting parameters and all have five rules.

Now I need to use a vba to say count all assets that are green, red, yellow. However, I am having difficulty. I have tried using the COLAC function which did work but only on cells of one color when they were in rows or columns not when they were mixed colours. Then I tried doing the index of color VBA which worked but does not work on my cells as they have data in from another source.

So I was wondering can the COLAC function use three format sources for search within a ravage or can it only be the one?

Foe example currently it is =COLAC(CELL TYPE,RANGE,FALSE)
Can it be =COLAC(CELL TYPE1/CELL TYPE2/CELL TYPE3/RANGE,FALSE)

Otherwise I'm going to have to add conditionally formatting again for each assert but add a cell where data is not connected and get the index number then do a count of all number of indexes. Which would take me forever as there is so many!

Any advice would be greatly appreciated.

Thanks

Gemma