.Formula1 error in logging Conditional Formatting

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
551
I'm logging by conditional formatting based on Jeff Weir's work (Sub FindCF() here). I kludged together a way to log it to a sheet using the code below:

Code:
For Each WhatIsIt In sht.Cells.FormatConditions
        Select Case TypeName(WhatIsIt)
            Case "FormatCondition"
            Set fc = WhatIsIt
            sht.Cells(Rows.Count, 1).End(xlUp).Offset(1) = _
            "Worksheet:§" & sht.name & _
            "§Type:§" & TypeName(WhatIsIt) & " | " & cfType(WhatIsIt.Type) & _
            "§Applies To:§" & Replace(WhatIsIt.AppliesTo.address, "$", vbNullString) & _
            "§Formula:§" & WhatIsIt.Formula1 & _
            "§Stop if True:§" & WhatIsIt.StopIfTrue
and then running Text to Columns using "§" as a delimiter.

However, when I attempted to tighten the code up, the WhatIs.Formula1 starts giving RTE 1004 (Application-defined or object-defined error) when I use the code below:
Code:
          Case "FormatCondition"
            With shtLog.Cells(Rows.Count, 1).End(xlUp)
              Set fc = WhatIsIt
              .Offset(1, 0) = sht.name ' sht name
              .Offset(1, 1) = TypeName(WhatIsIt)  ' type
              .Offset(1, 2) = cfType(WhatIsIt.Type)  ' Type of CF
              .Offset(1, 3) = Replace(WhatIsIt.AppliesTo.address, "$", vbNullString)  ' address
              .Offset(1, 4) = WhatIsIt.Formula1  ' Formula of CF
              .Offset(1, 5) = WhatIsIt.StopIfTrue  ' bln - Stop if True
            End With
I've attempted to use cf.Formula1 (like Jeff uses), but that doesn't help; I've attempted to point it back to the sheet with the actual CF and that doesn't help. Interestingly, in the Immediate Window, ?WhatIsIt.Formula1 gives the proper answer. I'm stumped!:banghead:

Any suggestions to fix the error (or a better way to log my CF to a sheet) would be most welcome!!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The formula is probably erroring when you put it in the cell so try prefixing it with an apostrophe:
Code:
.Offset(1, 4).Value2 = "'" & WhatIsIt.Formula1
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
551
I bow before your genius, sir! :pray:

Thanks so much, Rory!! That works perfectly (and makes sense too).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,600
Messages
5,445,414
Members
405,332
Latest member
jmavillarroel

This Week's Hot Topics

Top