Dr. Demento
Well-known Member
- Joined
- Nov 2, 2010
- Messages
- 618
- Office Version
- 2019
- 2016
- Platform
- 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:
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:
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!
Any suggestions to fix the error (or a better way to log my CF to a sheet) would be most welcome!!
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
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!
Any suggestions to fix the error (or a better way to log my CF to a sheet) would be most welcome!!