Storing Condition Format rules

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
From here I found this;

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

VBA Code:
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!!!!!!!!!!!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
The issue I have found with the code is.

With this line of code set to
VBA Code:
"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
VBA Code:
"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
VBA Code:
"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,
VBA Code:
=$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
VBA Code:
$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??
 

Watch MrExcel Video

Forum statistics

Threads
1,127,484
Messages
5,625,028
Members
416,065
Latest member
meiravmeron

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
Top