FormatCondition Operator

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
557
I'm futzing around with Jeff Weir's Easy way to back up CF formats and I'm trying to create a list of CF that can then be read into a sub and applied on other workbooks. For the most part everything works out fine, but I'm having trouble with the FormatCondition.Operator Property. According to MSDN, it's supposed to "Returns a Long value that represents the operator for the conditional format." However, for the life of me I can't figure out what property to use to get the Operator value when reading the CF formats from a workbook.

I've tried object.Operator but I get RTE 1004 (application/object-defined error). I recognize there may not be any way to pull out the Operator, but never know . . .

Code:
Dim obj As Object
Dim fc As FormatCondition

    For Each obj In sht.Cells.FormatConditions
        strAddress = Replace(obj.AppliesTo.address, "$", vbNullString)  ' ~~ Removes "$"

        Select Case TypeName(obj)
          Case "FormatCondition"
            Set fc = obj
            tbl_addNewRow tbl
            tbl_addData tbl, Now, 1                                   ' Timestamp
            tbl_addData tbl, sht.name, 2                              ' sht name
            tbl_addData tbl, TypeName(fc), 3                          ' Variable type
            tbl_addData tbl, cfConditionType(fc.Type), 4              ' Type of CF
            t[B]bl_addData tbl, fc.Operator, 5                           ' Operator  <-- Hut the wheck?[/B]
            tbl_addData tbl, strAddress, 6                            ' address
            tbl_addData tbl, "'" & fc.Formula1, 7                     ' Formula of CF
            tbl_addData tbl, obj.StopIfTrue, 8                        ' bln - Stop if True
       End Select
     Next obj
The subs with tbl_ prefix have to do with putting the data in the table. They can be ignored.

I had to Dim cf as a generic Object instead of declaring as FormatCondition because DataBars screw things up.
' See When is a FormatCondition not a FormatCondition? | Excel Matters

Any thoughts?

Thanks y'all.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
557
Bump? Does my question make sense? It does to me, but you know how well that works out :eek:
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,366
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
FormatCondition is the only one of the possible objects in the FormatConditions collection that actually has the Operator property, so you'd need to add a TypeName or TypeOf check to your code before trying to use it.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,037
Messages
5,508,927
Members
408,701
Latest member
Ucchik7

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top