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 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.
you look like the man. I'm trying to do something simular to what you have done here.
I want the hyperlinked cells on the overview sheet to turn red if any cell on any of the hyperlinked sheet is red, and green if there isn't a red cell. there is a lot of conditional formatting going of for the cells so hard to to duplicate into the one cell.
I have tried GetColor on the the sheet but just cant get it to get a response, I have got the Bankhead hyperlink cell on Farm overview (sheet 1) to turn red by Bankhead (sheet 6) cell AP4 with conditional formatting, but just need a formula to get a response from the above criteria
I have now tried adding this VBA but that isn't working either
not sure if there is an easier way or some part of the code is wrong
Thanks for your kind words.
I looked briefly at your file. Found couple of issues.
A) your conditional formatting are not consistent. For example Worksheet BankHead column O has 4 rules in conditional formatting. then your column Q only has 1 rule and column R has 3 rules. so, there is no clear pattern for a formula to determine the count. If your columns CF were having the same pattern of rules, then we could have used a simple formula in a helper cell for your overview sheet to turn red if any of those columns cells condition were met. For example this formula =SUMPRODUCT(--(C3:AL3<C1)*(LEN(C3:AL3)=5))-COUNTIFS(C3:AL3,"X") it checks if the cells are date, it then checks if the dates are smaller than the today's date, then finally if they are done which has X then it will exclude it. this formula you could have used for red color, if your columns had consistent rules. but they haven't, so this formula is returning incorrect value. This formula gives you an idea how you can make it work, by fixing your CF rules.
B) you have used two many unnecessary If functions with ISblank function to evaluate if the cells are not empty. This has caused your workbook to become sluggish. instead of =IF(ISBLANK(A5),"" you could first evaluate that If cell is not empty then do that without the use of ISBLANK function. The way it is now, it first evaulates every cell if it blank or not. so, you could use something like =IF(A5<>"",$A5-7*4,"").
C) TODAY functun is volatile, using it multiple time in everysheet is slowing down your workbook.
instead you can have one single cell with TODAY function and all other formulas are referenced to this single cell. in fact you could have one single named range with =TODAY() function and it is used in your formulas across the sheets.
D) you have pre-populated 500 rows in each of the sheets with the unnecessary formulas. I understand that you need to have those formulas in case if the data grows, however you can avoid those 500 cells with pre populated formula by using Excel Tables feature, which has dynamic nature that when you add new data all formulas from the above cells are automatically copied down. so your workbook will work much faster and more dynamic with Excel Tables rather than these 3500 rows with formulas which sucks the CPU.
Hope it helps.
Jamil
Thank you for this, i have been trying to trouble shoot my issues for the past week or so to no avail. The problem is I need to use quite a few different colors for conditional formatting. I have made sure that all the colors match in terms of RGB between the painted cells and the conditional cells. But after the first 9 (the 9/10 standard pallet colors) the math doesn't seem to be accurate any longer. Is there anyway you can take a look at my file and see if there is anything I can do to fix this or if you have any advice on this?Hi, perhaps you may have not read my earlier posts to similar issues.
Your conditional formatting range in the CF and inside UDF were not matching.
I change your CF applied ranges in a way to match the UDF and now it works, see attached.
Hi Jamil,
done everything you suggested, it has worked a treat but still cant get the overview cells to change to red or green from their relevant sheet
hope for some assistance
Great! now only little has remained.
Please see attached workbook. I have placed a formula next to the Bankhead icon in cell A5 of Overview sheet. this is the formula =SUMPRODUCT(--(Bankhead!C3:AL3<Bankhead!C1)*(LEN(Bankhead!C3:AL3)=5))-COUNTIFS(Bankhead!C3:AL3,"X")
then I used custom formatting and in custom formatting I put ;;; three semicolons, so that the value returned is not visible. Then I created a conditional formatting on cell B5 that If cell A5 is greater than zero then turn red.
You can replicate this with the other worksheet icons as well.
Hope it helps.
- Jamil
that is brilliant I am only an amateur at this piecing together the bit of knowledge I know to make this for my wife, not used tables before but I will look into correcting the spreadsheet with you recommendations
thanks a lot
Ade
