Is it possible to increment/decrement one cellvalue based on the background-color of a column of cells?

user_13607927 0
Viewed 129

For example i have a column of 10 cells, 4 of which have a green backgroundcolor and the others have none. (not necessarily in order)

Is it possible to get one cell to count the amount of cells have a green backgroundcolor in that column? If counting isn't possible is it possible to increment or decrement based on the backgroundcolor? Using my example it would mean that because there are 4 cells that have a green backgroundcolor, a different cell would have the value of 4, or 6 if i wanted to count the noncolored ones. (see picture for example without function)

i have tried IF statements or played around with conditional rules but i haven't got a working function for it yet.

Does anyone have an idea on how to do this?

Excel color counting

excel-formula excel
1 Answers

You would have to create a VBA function. This will do it:

Function CellColor(Target As Range) As Long
 ' gives the color of the target cell
 ' ranges from 0 (black) in hex to FFFFFF (white or no color)
 ' the hex range is BBGGRR (B = blue, G = green and R = red)
 ' example, bright red would be 0000FF or 255
 
  CellColor = Target.Interior.Color

End Function

This would be used like this: enter image description here

Array Function To count the number of cells of a certain color in a range, you need a VBA UDF that returns an array. You could use this version:

Function CellColors(Target As Range) As Variant()
   Dim resultArr() As Variant

   'Create a collection
   Dim col As New Collection

   'Adding values to collection
   On Error Resume Next
   For Each v In Target
       col.Add v.Interior.Color
   Next
   On Error GoTo 0

   'completing operation on each value and adding them to Array
   ReDim resultArr(col.Count - 1, 0)
   For i = 0 To col.Count - 1
       resultArr(i, 0) = col(i + 1)
   Next

   CellColors = resultArr

End Function

Then you can apply it across a range, like this:

= SUM( --(CellColors(A1:A10)<16777215) )

This formula is simply saying "count the non-white cells".

enter image description here

user_46171210
Related