CODEWITHSUNDEEP
excelvba
rohrl77
rohrl77

Reputation: 3337

How to get the FormatCondition Object

I want to read out the color that the conditional format sets for a particular cell.

Example,commentary, and code of what I've tried below. Note: Selection is simply a cell that contains a conditional format.

How can I get a refrence to a FormatCondition Object? What am I missing/ not seeing? Error on last line...

Sub FC_Test()
Dim fc As FormatConditions
Dim fco As Object
Dim c As Object
Dim myRng As Range
Set myRng = Selection 'Any cell with a conditional format

Debug.Print "FC Count: " & myRng.Resize(1, 1).FormatConditions.Count

'Finds all FC on the sheet
Set fc = Cells.FormatConditions
Debug.Print TypeName(fc) 'Returns: FormatConditions

'Finds first applied format condition...
'...oddly this is not a FormatCondition (member of hte FC collection),
' but the name of the type of format condition applied.. i.e. "ColorScale", etc.
Set c = Cells.FormatConditions(1)
Debug.Print TypeName(c) 'Returns: ColorScale

'Finds FC in selected range.
Set fc = myRng.Resize(1, 1).FormatConditions
Debug.Print TypeName(fc) 'Returns: FormatConditions
Debug.Print TypeName(fc.Item(1)) 'Returns: ColorScale
Set fco = fc(1)
Debug.Print TypeName(fco) 'Returns: ColorScale

Set fco = Nothing
For Each fco In fc
    Debug.Print TypeName(fco) 'Returns: ColorScale
Next fco

Dim fcs As FormatCondition
Set fcs = myRng.Resize(1, 1).FormatConditions(1) 'Type Mismatch:13

End Sub

Upvotes: 0

Views: 1527

Answers (2)

BrakNicku
BrakNicku

Reputation: 5990

FormatConditions collection can contain different type of objects: FormatCondition,Databar, ColorScale, IconSetCondition, ....

Those are different classes with different properties - the type of object depends on conditional formatting rule kind.

It means that it is not possible to get FormatCondition object when there is different (for example color scale) rule applied. To read color of the cell (no matter if it is normal or conditional formatting) Range.DisplayFormat can be used.

Upvotes: 2

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

FormatConditions object

With myRng.FormatConditions(1)
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ColorIndex = 19
    .Font.ColorIndex = 26
End With

Upvotes: 0

Related Questions