Forum Discussion
sum by color when colors are set by conditional formatting
Hi matt nipper,
since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.
the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the UDF
Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange.FormatConditions.Count If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then Bambo = True Exit For End If Next CF1 CF2 = 0 CF3 = 0 If Bambo = True Then For Each CFCELL In CellsRange dbw = CFCELL.FormatConditions(CF1).Formula1 dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1) dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1)) If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.
User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.
I hope this helps you.
Edit: uploaded file and updated the code to the correct one.
Hi, I have put some additional codes in the attached workbook that forces recalculation and that should take care of the issue you described.
is there a way to count color by CF when CF is not using a formula.
i have attached a my file. the CF is set by colmun and i need to count the cells by row.
i managed to get it to count with the following code but i have an auto sort macro in place and as soon as it sorts i get a #Value error.
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function
I tried to remove the auto sort to see if that worked and then the count is not correct.
Thanks,
JW
I checked the file is fine. It works in my machine.
Sometimes the #Value error gets triggered by some other Add-Ins or Personal.XLSB
before you open the file again, disable any add-ins and personal.xlsb file.
try to see if you have any file in this directory C:\Users\YOURUSERNAME\appdata\Roaming\Microsoft\Excel\XLSTART
if there is try to move it temporarily.
also if you have any Add-In Installed, try to disable the add-in.
Dear Jamil
I'm experiencing issue with sum of CF cells. I have cells that colors in a dinamic way:
If the sum is less than D3, color the cells in green
If the sum is between D3 and D3+D4 color them in yellow
If the sum is between D3+D4 and D3+D4+D5 color them in red
Colouring works fine, but when I try to sum the value per color, only summing green works, the others return in a #VALUE error.
I believe I've done not a proper color formatting.
Can you please help out?
Thank you.
- Hi,
You need to look at my earlier posts and see how did I provided solution for other users. Sorry, don't have time to build a custom new solution particular for your file. Hi Jamil,
I am trying to highlight cells with duplicated text that also have an overlap in dates on a project planner. I have tried to count the name of the team involved and the number of coloured cells on the same date and if the return is above 1, then highlight the team cell (Column G) in red to show the duplication.
My trouble is finding the duplication overlap on a number of highlighted dates.
I feel i may be over complicating this though.
I am not sure if the duplication option in the Conditional Formatting can do this.
I have attached a dummy.
