Need Help with VBA removing conditional formatting

[Rick1006]

Hello -

Sorry if this has been posted before, however, I didn't see anything for this type of issue.

I am trying to remove the conditional formatting while leaving the color coding. I used the following vba code prior and it worked - now it's removing the formatting but turning the cell colors black. They should remain either blank, red or green depending on cell value. Any help would be appreciated

Sub removeConditionalFormattingButKeepColors()
Dim cell As Range

For Each cell In Selection
cell.Interior.Color = cell.DisplayFormat.Interior.Color
Next

Selection.FormatConditions.Delete

MsgBox "Done removing conditional formatting rules."

End Sub

 

[johnnyL]

See if this works for you:

Sub Remove_CF_Keep_Format()
'
    Dim Cel As Range
    Dim Rng As Range
    Dim xTxt As String
'
    On Error Resume Next
'
    If ActiveWindow.RangeSelection.Count > 1 Then
        xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
        xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
'
    Set Rng = Application.InputBox("Select range:", "Please enter desired Range", xTxt, , , , , 8)
'
    If Rng Is Nothing Then Exit Sub
'
    For Each Cel In Rng
        With Cel
            .Font.FontStyle = .DisplayFormat.Font.FontStyle
            .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
            .Interior.Pattern = .DisplayFormat.Interior.Pattern
'
            If .Interior.Pattern <> xlNone Then
                .Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex
                .Interior.Color = .DisplayFormat.Interior.Color
            End If
'
            .Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade
            .Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade
        End With
    Next
'
    Rng.FormatConditions.Delete
End Sub

 

[Rick1006]

Thanks, but that too changed the cells to black. See attached image. Could this be due to the type of XLS workbook I am using? Should it be a macro enabled WB?

 

[Rick1006]

Anyone?????