VBA to Clear everything except the Conditional Format coding I have which changes colors for cells.

[Livin404]

Hello,

I'm finally getting to the end, and I suddenly realized I am indeed going to have to use custom formatting.

I have been using the macro below which clears everything including the formatting. If I use the "Clear Contents" (image) it leaves all the formatting. Including the colors and boarders. Is there something I can write which Clears everything but leaves the coding that changes colors for cells. Basically it I'm left with a blank sheet, but when I run the main Macro it will pick up the that I have for

Sub Clear_68()
Dim ws As Worksheet
For Each ws In Sheets(Array("Outbound"))
ws.Cells.Clear
ws.Cells.RowHeight = 14.4
ws.Cells.ColumnWidth = 8.11
Next ws
End Sub

 

[HaHoBe]

Hi Livin404,

using an Array for a single sheet is not whaqt I would expect. Build a range consisting of all cells except those with CF like

Sub Clear_68()
' https://www.mrexcel.com/board/threads/vba-to-clear-everything-except-the-conditional-format-coding-i-have-which-changes-colors-for-cells.1229124/
Dim rngCF As Range
Dim rngDel As Range
Dim rngCell As Range

With Sheets("Outbound")
  On Error Resume Next
  Set rngCF = .Cells.SpecialCells(xlCellTypeAllFormatConditions)
  Err.Clear
  On Error GoTo 0
  If Not rngCF Is Nothing Then
    For Each rngCell In .UsedRange
      If Intersect(rngCell, rngCF) Is Nothing Then
        If rngDel Is Nothing Then
          Set rngDel = rngCell
        Else
          Set rngDel = Union(rngDel, rngCell)
        End If
      End If
    Next rngCell
    If Not rngDel Is Nothing Then
      rngDel.Clear
      Set rngDel = Nothing
    End If
  End If
  .Cells.RowHeight = 14.4
  .Cells.ColumnWidth = 8.11
End With
End Sub

Merged Cells? Really?

Ciao,
Holger

 

[HaHoBe]

Hi Livin404,

why not add the CF after clearing the contents?

You would need to adjust the code to suit as I can't figure your CF out of a picture:

Sub AddCF2Outbound()
Dim strCF As String
Dim strWhichRange As String
Dim strWhichCell As String
Dim strWhatContent As String

strWhichRange = "M22:M33"
strWhichCell = "A22"
strWhatContent = ""

With Worksheets("Outbound").Range(strWhichRange)
  .FormatConditions.Delete
  strCF = "=$" & strWhichCell & "=" & Chr(34) & strWhatContent & Chr(34)
  .FormatConditions.Add Type:=xlExpression, Formula1:=strCF
  .FormatConditions.Item(1).Interior.Color = RGB(255, 255, 0)
End With
End Sub

Holger