CODEWITHSUNDEEP
excelvbaformattingcopy-pasteconditional-formatting
Dove_pigeon
Dove_pigeon

Reputation: 3

VBA: remove conditional formatting outside print area

I have many sheets with complex conditional formatting - I would like to automate a way to clear all conditional formatting outside the print area. Does anyone know how to go about this?

In other forums I only see solutions that clear conditional formatting from entire sheets rather than selected ranges. My goal is to automatically copy over tables from other sheets below another table without the conditional formatting of the target sheet. It is too much work to manually modify the ranges of the conditional formatting. Thanks!

Upvotes: 0

Views: 80

Answers (1)

taller
taller

Reputation: 18898

  • Below code clears the conditional formatting of table area (range) on the destination sheet.
Option Explicit
Sub Demo()
    Dim srcRng As Range, desRng As Range, oCF As FormatCondition
    Set srcRng = Sheets("Sheet1").Range("B3:F10") ' source table, modify as need
    Set desRng = Sheets("Sheet2").Range("D5") ' destination top-left cell
    With desRng.Resize(srcRng.Rows.Count, srcRng.Columns.Count)
        For Each oCF In .FormatConditions
            oCF.Delete
        Next
    End With
    srcRng.Copy desRng
End Sub

Update:

Question: Do you know how to replace the destination range with the print area

  • There is not a direct method to make an exception range selection (eg. select all cells excep print area). A workaround solution is to merge the range one by one using a UDF.
Option Explicit
Sub Demo()
    Dim desRng As Range, oCF As FormatCondition
    Dim sRng As String, oSht As Worksheet
    Dim sRow As Long, sCol As Long
    Dim eRow As Long, eCol As Long
    Set oSht = Sheets("Sheet1")
    With oSht
        sRng = .PageSetup.PrintArea
        If Len(sRng) > 0 Then
            With .Range(sRng)
                sRow = .Row
                sCol = .Column
                eRow = .Cells(.Cells.Count).Row
                eCol = .Cells(.Cells.Count).Column
            End With
            Set desRng = UnionRng(oSht, desRng, True, 1, sRow - 1)
            Set desRng = UnionRng(oSht, desRng, True, eRow + 1, .Rows.Count)
            Set desRng = UnionRng(oSht, desRng, False, 1, sCol - 1)
            Set desRng = UnionRng(oSht, desRng, False, eCol + 1, .Columns.Count)
            For Each oCF In desRng.FormatConditions
                oCF.Delete
            Next
        End If
    End With
End Sub
Function UnionRng(oSht As Worksheet, oRng As Range, _
            bRowMode As Boolean, iStart As Long, iEnd As Long) As Range
    Set UnionRng = Nothing
    If iEnd >= iStart Then
        Dim NewRng As Range
        With oSht
            If bRowMode Then
                Set NewRng = .Range(iStart & ":" & iEnd)
            Else
                Set NewRng = .Range(.Cells(1, iStart), .Cells(1, iEnd)).EntireColumn
            End If
        End With
        If oRng Is Nothing Then
            Set UnionRng = NewRng
        Else
            Set UnionRng = Application.Union(NewRng, oRng)
        End If
    End If
End Function

Upvotes: 0

Related Questions