Copying


Sub CopyConditionalFormats( 
Dim m_FormatConditions As Collection
Dim oWsh As Worksheet
Dim oFormatCondition As FormatCondition
Dim lwshcount As Long
Dim lcount As Long
Dim ltotalcount As Long
Dim scellrange As String
Dim swshname As String
Dim sformula1 As String
Dim enOperator As XlFormatConditionOperator
Dim enType As XlFormatConditionType
Dim returnCondition As FormatCondition

    Set m_FormatConditions = New Collection
    ltotalcount = 1
    For lwshcount = 1 To ThisWorkbook.Sheets.Count
        Set oWsh = ThisWorkbook.Sheets(lwshcount)
        For lcount = 1 To oWsh.Cells.FormatConditions.Count
            Set oFormatCondition = oWsh.Cells.FormatConditions(lcount)
            
            Call m_FormatConditions.Add(Item:=oFormatCondition, Key:="Item-" & ltotalcount)
            
            Debug.Print oWsh.Name & " - " & oFormatCondition.AppliesTo.Address
            
            ltotalcount = ltotalcount + 1
        Next lcount
    Next lwshcount
    
    For lcount = 1 To m_FormatConditions.Count
        Set oFormatCondition = m_FormatConditions(lcount)

        swshname = oFormatCondition.AppliesTo.Worksheet.Name
        Set oWsh = ThisWorkbook.Sheets(swshname)
        
        scellrange = oFormatCondition.AppliesTo.Cells.Address
        enType = oFormatCondition.Type
        enOperator = oFormatCondition.Operator
        sformula1 = oFormatCondition.Formula1
        
        returnCondition = oWsh.Range(scellrange).FormatConditions.Add( _
            Type:=enType, _
            Operator:=enOperator, _
            Formula1:=sformula1)
        
    Next lcount
End Sub


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext