And I forgot to include that the code was based on Bob Phillips' function IsCFMet - I just
high-jacked it for my CFColor function.

HTH,
Bernie
MS Excel MVP


'---------------------------------------------------------------------
Public Function CFColor(rng As Range) As Long
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long
Dim IsCFMet As Boolean


Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
IsCFMet = False
Select Case oFC.Operator
Case xlEqual
IsCFMet = rng.Value = oFC.Formula1
Case xlNotEqual
IsCFMet = rng.Value < oFC.Formula1
Case xlGreater
IsCFMet = rng.Value oFC.Formula1
Case xlGreaterEqual
IsCFMet = rng.Value = oFC.Formula1
Case xlLess
IsCFMet = rng.Value < oFC.Formula1
Case xlLessEqual
IsCFMet = rng.Value <= oFC.Formula1
IsCFMet = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
IsCFMet = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
If IsCFMet Then
CFColor = oFC.Interior.ColorIndex
Exit Function
End If

Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
IsCFMet = rng.Parent.Evaluate(sF1)
End If
If IsCFMet Then Exit Function
Next oFC
End If 'rng.FormatConditions.Count 0


End Function

wrote in message
...
I have a range of cells to which I've applied a simple red-to-green
color scale using the Conditional Formatting menu in Excel 2007. I
want to read the resulting colors that Excel has applied to the cell
interiors from VBA to use these colors for an associated graph. As a
test, I wrote the following worksheet function to see what colors
Excel had assigned:

Function CellColor(c As Range) As Variant
Application.Volatile
CellColor = c(1, 1).Interior.Color
End Function

Regardless of the cell's color, the returned value is always 16777215;
if I use the ColorIndex property, the returned value is (-4142). Do I
need to decode these to RGB values? If so, how? I don't need to solve
this for prior versions of Excel as I realize the color handling was
much different.

Thanks for your help.

Bob