Working with coloured cells
OK. I know Excel wasn't originally designed this way but a lot of users, especially in the financial sector, uses different fill colours to indicate different types of data or different quality data.
It would help enormously therefore there were worksheet functions which could work with coloured cells without resorting to VBA e.g. a SUMIF() function which worked with interior colours so we could sum all red cells for example.
(This would also mean adding an additional option to allow recalculation if the formatting of cells was changed - something which we can do at present through VBA but doesn't take place automatically.)
Malcolm G
shared this idea
7 comments
-
Clare
commented
We have an app which helps people to make calculations based on coloured cells: https://www.doofa.com/products/colorcalc - it's called ColorCalc and, once installed, is available from the Excel toolbar.
-
Scott
commented
Note the functions recalculation won't be triggered by cell interior color changes. Hence users will change cell colors then check the formula and see the wrong result. Also very similar colors will not be the same. (or if they attach recalculation to that action then it might mean there is a lot more recalculation going on).
Sounds like it could cause a lot of frustration for some users.
-
Graeme Hodnett commented
I have had to come up with a VBA solution to do exactly what you have described. It would be great if the function was added to the SUMIF group. The macro works well but because it is not a system function it takes a bit longer to calculate.
Function SumIfColor(rcolor As Range, rRange As Range, Optional SUM As Boolean)
'Application.Volatile (SumIfColor)
Dim rCell As Range
Dim lCol As Long
Dim vResult''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rcolor.Interior.ColorIndexIf SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol 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
Next rCell
End If
SumIfColor = vResult
End Function -
Stephanie Hawley
commented
I was about to make the same suggestion...I would like to see this with Countif as well.
-
Jan Wx, NZ
commented
RobS - specified range I hope ;)
-
RobS
commented
Related, I'd like to see a =GETCOLOR(A1) function to return the RGBValue of the specified cell.
-
Jan Wx, NZ
commented
Go and have a look at doofa.com; it's an add-in, that's the down-side