Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatted numbers add based on color?
I'm wondering how to Add red numbers together in a column and blue numbers in
a column. I've conditionally formatted them based on a text string (CAD/USD) but the macro I've found isn't working, I believe it's not working because the cells are conditionally formatted. Not my code, forget were I got it from. Function SumColor(rColor As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatted numbers add based on color?
I believe it's not working because the cells are conditionally formatted.
I believe you're right. The last time I checked it did not seem possible to directly check the formatting that conditional formatting was currently applying. I think the only thing you can do is extract the 'conditions', evaluate their current values and then extract what formats would be applied based on them. All this using the range FormatConditions object. Quite the hassle. -- Jim "Mitch" wrote in message ... I'm wondering how to Add red numbers together in a column and blue numbers in a column. I've conditionally formatted them based on a text string (CAD/USD) but the macro I've found isn't working, I believe it's not working because the cells are conditionally formatted. Not my code, forget were I got it from. Function SumColor(rColor As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatted numbers add based on color?
Any suggestions on how to get the color change based on a text string? (Even
if you can provide just the function in the scripting language I can probably get the rest done. Well how do you get it to pick the column to the left of where you start. C10 has the numbers that I want changed to red or blue, and C9 has the text USD or CDN. Then I have to add up the C10 values based on Red or Blue. Wish there was some good documention on the Excel/VBA .. or haven't found it yet. "Jim Rech" wrote: I believe it's not working because the cells are conditionally formatted. I believe you're right. The last time I checked it did not seem possible to directly check the formatting that conditional formatting was currently applying. I think the only thing you can do is extract the 'conditions', evaluate their current values and then extract what formats would be applied based on them. All this using the range FormatConditions object. Quite the hassle. -- Jim "Mitch" wrote in message ... I'm wondering how to Add red numbers together in a column and blue numbers in a column. I've conditionally formatted them based on a text string (CAD/USD) but the macro I've found isn't working, I believe it's not working because the cells are conditionally formatted. Not my code, forget were I got it from. Function SumColor(rColor As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add value of cells based on the formatted fill color | Excel Worksheet Functions | |||
Conditional format a cell based on color of another | Excel Discussion (Misc queries) | |||
sum by conditional formatted color | Excel Discussion (Misc queries) | |||
calculate numbers based on font color? | Excel Worksheet Functions | |||
Conditional Formatted Cell Color Index | Excel Programming |