CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

Ignore items in a formatconditions collection that are not formatcondition?

The following code works for most sheets in my workbook:

Function IsHighlighted() As Boolean

    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        Dim rUsedRange As Range
        Set rUsedRange = wks.UsedRange

        With rUsedRange
            Dim bHighlighted As Boolean

            Dim fc As Object
            For Each fc In .FormatConditions
                If fc.Interior.Color = RGB(255, 0, 0) And fc.Font.Color = RGB(255, 255, 0) Then
                    bHighlighted = True
                    Exit For
                End If
            Next fc

            If bHighlighted = True Then
                Exit For
            End If
        End With
        Debug.Print (wks.Name & "," & rUsedRange.FormatConditions.count)
    Next wks

    IsHighlighted = bHighlighted
End Function

But some of my sheets contain icon sets.

Apparently: The FormatConditions collection includes FormatCondition, DataBar, AboveAverage, ColorScale, UniqueValues, Top10 and IconSetCondition objects, not just FormatCondition objects. For more context, see: For each fc in range.formatconditions fails. Why?

Some of these don't have .Interior.Color so the if statement throws an error.

What is a nice way I can skip all of these objects in the formatconditions collection that are not of the type formatcondition?

I tried using VarType(fc) to determine the obejct type, but it only retuns a 9 which says that fc is some kind of object.

Upvotes: 1

Views: 99

Answers (1)

El Scripto
El Scripto

Reputation: 576

Did you try the Typename function, for example:

Dim str as string.
Msgbox Typename(str)  ' ->>> "String"

Upvotes: 1

Related Questions