Conditional Formatted numbers add based on color?

[Guest]

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

 

[Jim Rech]

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.

 

[Guest]

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.