Storing Condition Format rules

[julhs]

From here I found this;

https://www.extendoffice.com/documents/excel/4805-excel-print-all-conditional-formatting-rules.html

Sub M_snb()
Dim xRg As Range, xCell As Range
Dim xFormat As Object
Dim xFmStr, xFmAddress As String
Dim xDic As New Dictionary
Dim xSpArr, xOperatorArr

On Error Resume Next

Set xRg = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)

If xRg Is Nothing Then Exit Sub

xDic.Item("Title") = "Type|Typename|Range|StopIfTrue|Operator|Formula1|Formula2|Formula3"If xSpArr.Count = 0 Then
xSpArr = Split("Cell Value|Expression|Color Scale|DataBar|Top 10|Icon Sets||Unique Values|Text|Blanks|Time Period|Above Average||No Blanks||Errors|No Errors|||||", "|")
xOperatorArr = Split("xlBetween|xlNotBetween|xlEqual|xlNotEqual|xlGreater|xlLess|xlGreaterEqual|xlLessEqual", "|")
End If

For Each xCell In xRg

Set xFormat = xCell.FormatConditions(1)

xFmAddress = xFormat.AppliesTo.Address

If Not xDic.Exists(xFmAddress) Then
xDic.Item(xFmAddress) = xFormat.Type & "|" & xSpArr(xFormat.Type - 1) & "|" & xFmAddress & "|" & xFormat.StopIfTrue
If Not IsEmpty(xFormat.Operator) Then
xDic.Item(xFmAddress) = xDic.Item(xFmAddress) & "|" & xOperatorArr(xFormat.Operator - 1)
End If

If Not IsEmpty(xFormat.Formula1) Then

xDic.Item(xFmAddress) = xDic.Item(xFmAddress) & "|'" & xFormat.Formula1

End If

End If

Next

If ActiveWorkbook.Worksheets("FmCondictionList") Is Nothing Then

Sheets.Add.Name = "FmCondictionList"

End If

Sheets("FmCondictionList").Cells(1).Resize(xDic.Count) = Application.Transpose(xDic.items)

Sheets("FmCondictionList").Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|
End Sub

It worked exactly as intended for the sheet I was on, (“Active Sheet”) when I first installed the “Module” and it ran for first time.

But changes to the code I’ve made, do not show “Condition Formating” for the WHOLE Workbook , how is it possible to tweek it so it will work for the whole Workbook and is listed per sheet?

A new separate sheet of “Condition Formating” for each Sheet within the Workbook would be compromise but certainly OK, it would only increase the Book size by 4 Sheets.

Either option is more than expectable compared to Pasting & Copying EVERY individual rule from ALL the desired Sheets to a Word Doc for safe keeping; as I have done in the past!!!!!!!!!!!

 

[julhs]

May be I jumped the gun with "It worked exactly as intended". It has only listed 31 of the 50 on the Sheet, I'm still looking into the ones that are missing and possible reasons why.
My first thought is that there is very little difference between some rules and another; but that shouldn't make any difference, if it did this code is pretty useless.

 

[julhs]

The issue I have found with the code is.

With this line of code set to

"Set xFormat = xCell.FormatConditions(1)"

if there is more than ONE CF that has the SAME (Applies To) cell/range it is of course ONLY picking up the FIRST CF formula for that cell/range,

eg. If A=x, (applies to) C1:C5, If A=y, (applies to) C1:C5, If A=z, (applies to) C1:C5; it is only listing, A=x, (applies to) C1:C5.

However by changing to

"Set xFormat = xCell.FormatConditions(2)"

and running code again it lists just the 2nd CF, If A=y, (applies to) C1:C5

Run code again with

"Set xFormat = xCell.FormatConditions(3)"

3rd CF is listed, so one so one etc

Only problem with this is, I’ve got to run the code for 1, 2, 3…?? times depending on the max number of CF that have the same “Applied To” cell/range ref.
Then the individual lists have to be the combined/compared!!

Also; one odd result or lack of, was where I had,

=$D$20:$D$22, (Applies to) ISNUMBER(D20:D22) and =$E$20:$E$22 (Applies to) ISNUMBER(E20:E22),

neither where listed. But when I changed

$D$20:$D$22 to $D$20:$D$22,$D$24

it WAS listed, same went for $E$20:$E$22,$E$24.

I’ve done lots of testing as to what is happening and ways of "work around" it, but editing the original code to over-come the issues is WAY BEYOND me.

Has anyone got some suggestions??