Record Conditional Formatting in a Template Workbook

[atrels]

My aim is to choose a workbook with the Dialog Picker, to open and analyse this workbook, open a template workbook and record the locations, type ect... that each sheet that contains conditional formatting the template workbook.

Here are the two Scripts I am using.

Sub Analyses0()

Dim dialogBox As FileDialog
Dim sourceFullName As String
Dim wsk As Worksheet

'Open dialog box to select a file
Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)

dialogBox.AllowMultiSelect = False
dialogBox.Title = "Select Workbook to Analyse"

If dialogBox.Show = -1 Then
    sourceFullName = dialogBox.SelectedItems(1)
Else
    Exit Sub
End If

'''''''''''''''''''''''''
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    
Workbooks.Open ("C:\Users\andre\Desktop\Template.xlsx")

Set WB1 = ActiveWorkbook

Workbooks.Open sourceFullName

Set WB2 = ActiveWorkbook

'count the number of worksheets in the workbook
sheet_count = ActiveWorkbook.Worksheets.Count

'loop through the worksheets in the workbook
For a = 1 To sheet_count

    'code that you want to run on each sheet
    
    'simple message box that outputs the name of the sheet
    MsgBox ActiveWorkbook.Worksheets(a).Name
    
    Call Conditional_Formatting_Record

Next a
                      
End Sub

And

Sub Conditional_Formatting_Record()
    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 cl In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions)

            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
         
        WB1.Sheets("CF1").Cells(1).Resize(.Count) = Application.Transpose(.items)
        WB1.Sheets("CF1").Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|"
    End With
End Sub

It allows one to select the workbook, open the template, but I am struggling to get the conditional formatting (Conditional_Formatting_Record) output into the template workbook.

Any help would be appreciated. Thank you.

 

[GWteB]

This code is likely to do what you're after ...

Sub Analyses0_r2()

    Dim dialogBox As FileDialog
    Dim sourceFullName As String
    Dim oWs As Worksheet

    'Open dialog box to select a file
    Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)

    dialogBox.AllowMultiSelect = False
    dialogBox.Title = "Select Workbook to Analyse"

    If dialogBox.Show = -1 Then
        sourceFullName = dialogBox.SelectedItems(1)
    Else
        Exit Sub
    End If

    '''''''''''''''''''''''''

    Dim WB1         As Workbook
    Dim WB2         As Workbook
    Dim oWsOutput   As Worksheet

    Set WB1 = Workbooks.Open("C:\Users\andre\Desktop\Template.xlsx")
    
    Set oWsOutput = WB1.Sheets("Sheet1")            ' << change to suit
    
    Set WB2 = Workbooks.Open(sourceFullName)
    
    'loop through the worksheets in the workbook
    For Each oWs In WB2.Worksheets
        
        'code that you want to run on each sheet
        
        'simple message box that outputs the name of the sheet
        MsgBox oWs.Name
        
        Call Conditional_Formatting_Record(argShtToAnalyze:=oWs, _
                                           argShtForOutput:=oWsOutput)
    Next oWs
End Sub

and

Sub Conditional_Formatting_Record(ByVal argShtToAnalyze As Worksheet, ByVal argShtForOutput As Worksheet)

    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 cl In argShtToAnalyze.Cells.SpecialCells(xlCellTypeAllFormatConditions)

            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

        argShtForOutput.Cells(1).Resize(.Count) = Application.Transpose(.items)
        argShtForOutput.Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|"
    End With
End Sub