Record Conditional Formatting in a Template Workbook

atrels

New Member
Joined
Aug 2, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

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

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
This code is likely to do what you're after ...

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

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,031
Members
416,892
Latest member
Bensch

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