.Formula1 error in logging Conditional Formatting

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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!:oops:

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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
I bow before your genius, sir! :pray:

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

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top