Nanogirl21
Active Member
- Joined
- Nov 19, 2013
- Messages
- 330
- Office Version
- 365
- Platform
- Windows
I have an old code that was able to report all Conditional Formatting rules on the active sheet into a new sheet as text infromation. For some reason I can't seem to get it to work anymore. I am not recieving an error, but the code isn't reporting the Conditional Formatting rules, only the headers. Can someone please take a look.
EXPECTED RESULTS SHOWS ALL CONDITIOANL FORMATTING RULES THAT WAS ON THE ORGINAL ACTIVE SHEET. THE RESULTS SHOULD BE ON A ON NEW SHEET CALLED FINAL
Code:
Sub Conditional_Formattin_Rules()
Dim sp As Variant
Dim cl As Range
Dim cf As Variant
Dim c00 As String
On Error Resume Next
sp = Split("Cell Value|Expression|Color Scale|DataBar|Top 10?|Icon Sets||Unique Values|Text|Blanks|Time Period|Above Average||No Blanks||Errors|No Errors|||||", "|")
With CreateObject("scripting.dictionary")
.Item("titel") = "Type|Typename|Range|StopIfTrue|Formula1|Formula2|Formula3"
For Each rCell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
For Each cf In cl.FormatConditions
c00 = ""
c00 = cf.Formula1
If .exists(cf.AppliesTo.Address) Then
If InStr(.Item(cf.AppliesTo.Address), c00) = 0 Then .Item(cf.AppliesTo.Address) = .Item(cf.AppliesTo.Address) & "|'" & c00
Else
.Item(cf.AppliesTo.Address) = cf.Type & "|" & sp(cf.Type) & "|" & cf.AppliesTo.Address & "|" & cf.StopIfTrue & "|'" & c00
End If
Next
Next
Sheets.Add.Name = "FINAL"
Sheets("FINAL").Cells(3, 1).Resize(.Count) = Application.Transpose(.items)
Sheets("FINAL").Cells(3, 1).CurrentRegion.Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|"
End With
End Sub
Function FCTypeFromIndex(lIndex As Long) As String
Select Case lIndex
Case 12: FCTypeFromIndex = "Above Average"
Case 10: FCTypeFromIndex = "Blanks"
Case 1: FCTypeFromIndex = "Cell Value"
Case 3: FCTypeFromIndex = "Color Scale"
Case 4: FCTypeFromIndex = "DataBar"
Case 16: FCTypeFromIndex = "Errors"
Case 2: FCTypeFromIndex = "Expression"
Case 6: FCTypeFromIndex = "Icon Sets"
Case 14: FCTypeFromIndex = "No Blanks"
Case 17: FCTypeFromIndex = "No Errors"
Case 9: FCTypeFromIndex = "Text"
Case 11: FCTypeFromIndex = "Time Period"
Case 5: FCTypeFromIndex = "Top 10?"
Case 8: FCTypeFromIndex = "Unique Values"
Case Else: FCTypeFromIndex = "Unknown"
End Select
End Function
EXPECTED RESULTS SHOWS ALL CONDITIOANL FORMATTING RULES THAT WAS ON THE ORGINAL ACTIVE SHEET. THE RESULTS SHOULD BE ON A ON NEW SHEET CALLED FINAL