Excel 2007 conditional formatting - how to get cell color?

[bob.chatham]

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

 

[ShaneDevenshire]

Hi,

You are looking at cell colors you are not looking a conditional formatting
colors:

With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With

However, you may be able to retrieve the info on what the conditional format
is set for all the cells is but I don't believe you can retrieve what the
current cells color is. For one thing you can apply multiple conditional
formats to the same cell at the same time. For example it is possible in
2007 to format the cell with a Data Bar and a Color Scale and an Icon Set at
the same time. And have them all display at the same time. Similarly with
other combinations.

 

[Bernie Deitrick]

To return the format applied to the cell by Conditional Formatting, you need to first determine if
the format condition is met. Try the code below


HTH,
Bernie
MS Excel MVP


Sub TestCFColorMacro()
MsgBox CFColor(ActiveCell)
End Sub

'---------------------------------------------------------------------
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

 

[Bernie Deitrick]

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