Peter,

The OP mentioned that the colours would change dynamically with conditional
formatting. That code you show will not get this, it gets the cell/text
colour. See other posts in this thread to see how that needs to be
addressed.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


""Peter Huang" [MSFT]" wrote in message
...
Hi

I think we may try to use the VBA to sum the values.

1. the text of the value is Red.

Sub SumFontRed()
Dim rg As Range
Dim i As Integer
For i = 2 To 7
Dim sum As Integer
Set rg = Cells(i, 1)
If rg.Font.ColorIndex = 3 Then
sum = sum + rg.Value2
End If
Next
MsgBox sum
End Sub

2. the backgroud of the cell is in Red.
Sub SumBackgroudRed()
Dim rg As Range
Dim i As Integer
For i = 2 To 7
Dim sum As Integer
Set rg = Cells(i, 1)
If rg.Interior.ColorIndex = 3 Then
sum = sum + rg.Value2
End If
Next
MsgBox sum
End Sub

NOTE: here the ColorIndex = 3 means it is red.

To get the value conveniently, we may try to use the Record Macro function
to record a macro to see when we set the text to red, what is the macro
code recorded.
If you have any concern on this issue, pleaes feel free to let me know.
Thanks!



Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.