Remove Conditional Formatting but keep format

[sgg03246]

Hi there,
Does anyone knows how i can remove Conditional Formatting but keep the format which has been done via conditional format?

Thanks.

 

[cyrilbrd]

Hi,

have you tried to copy the needed cells. Select one of the copied Cond Form cells and push F5. Now click Special and then check "Conditional Formats" and then "Same" and Ok. Now go to Format / Conditional Formatting and delete all conditions

 

[sgg03246]

Thanks for your reply.
But after copying the cells, i can't paste and still keep the formats.

When i click F5, Go To Specials will indeed bring me to the cells with formulae in condition format but deletion will remove all formatting.

Pls let me know if i've not follow the instructions correctly. Thanks.

 

[cyrilbrd]

what version of excel are you using?

 

[sgg03246]

Excel 2010

 

[cyrilbrd]

give it a try:

Sub CopyOnlyFormatCondionColorSAS()
Dim c As Range
Dim x As Long
On Error Resume Next
For Each c In ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeAllFormatConditions)
x = c.FormatConditions(1).Interior.ColorIndex
c.FormatConditions.Delete
c.Interior.ColorIndex = x
Next c
End Sub

although I had concerns with it, hence it copied the format on every cells... Try on a non important file.

this one is from Mickericson:
Module 1:

Option Explicit

Sub convertSheet()
    Dim sheetToConvert As Worksheet
    Dim rngConditionalFormatted As Range
    Dim uiRange As Range
    
    On Error Resume Next
    Set uiRange = Application.InputBox("Select a Worksheet to convert", Type:=8)
    On Error GoTo 0
    If uiRange Is Nothing Then Exit Sub
    If MsgBox("Convert " & Trim(Left(Application.Substitute(uiRange.Address(, , , True), "$", String(255, " ")), 255)) & " ?", _
            vbYesNo) = vbNo Then Exit Sub
    Set sheetToConvert = uiRange.Parent
    'Set sheetToConvert = ThisWorkbook.Sheets("Sheet1"): Rem adjust
    
    On Error Resume Next
    Set rngConditionalFormatted = sheetToConvert.Cells.SpecialCells(xlCellTypeAllFormatConditions)
    On Error GoTo 0
    
    If Not rngConditionalFormatted Is Nothing Then
        Call ConvertCFtoBaseFormat(rngConditionalFormatted)
    Else
        MsgBox "no cf"
    End If
End Sub

Sub ConvertCFtoBaseFormat(rangeToConvert As Range)
    Dim oneCell As Range
    Application.ScreenUpdating = False
    For Each oneCell In rangeToConvert
        If CFormatMet(oneCell) <> 0 Then
            Call CopyCFto(oneCell, CFormatMet(oneCell))
        End If
        oneCell.FormatConditions.Delete
    Next oneCell
    Application.ScreenUpdating = True
End Sub

Sub CopyCFto(sourceCell As Range, CFConditionMet As Long, Optional destinationCell As Range)
    Rem Set destination cell base formatting = source cell conditional format #
    If destinationCell Is Nothing Then Set destinationCell = sourceCell.Cells(1, 1)
    Dim anEdge As Variant
    With sourceCell.Cells(1, 1).FormatConditions(CFConditionMet)
        With .Font
            If Not IsNull(.Bold) Then destinationCell.Font.Bold = .Bold
            If Not IsNull(.Italic) Then destinationCell.Font.Italic = .Italic
            If Not IsNull(.Underline) Then destinationCell.Font.Underline = .Underline
            If Not IsNull(.Strikethrough) Then destinationCell.Font.Strikethrough = .Strikethrough
            If Not IsNull(.ColorIndex) Then destinationCell.Font.ColorIndex = .ColorIndex
        End With
        
        On Error Resume Next
        For Each anEdge In Array(xlLeft, xlRight, xlTop, xlBottom)
            With .Borders(anEdge)
                If Not IsNull(.LineStyle) Then destinationCell.Borders(anEdge).LineStyle = .LineStyle
                If Not IsNull(.Weight) Then destinationCell.Borders(anEdge).Weight = .Weight
                If Not IsNull(.ColorIndex) Then destinationCell.Borders(anEdge).ColorIndex = .ColorIndex
            End With
        Next anEdge
        On Error GoTo 0
        
        With .Interior
            If Not IsNull(.PatternColorIndex) Then _
                    destinationCell.Interior.PatternColorIndex = .PatternColorIndex
            If Not IsNull(.Pattern) Then destinationCell.Interior.Pattern = .Pattern
            If Not IsNull(.ColorIndex) Then destinationCell.Interior.ColorIndex = .ColorIndex
        End With
    End With
End Sub

Function CFormatMet(oneCell As Range) As Long
    Rem which of the three conditional formatting conditions is met
    Rem given a cell, returns the number of the conditional format condtion that is met
    Rem if CF not engaged, returns 0
    Dim testFormula As String, tempFormula As String
    Dim i As Long
        With oneCell
            For i = 1 To .FormatConditions.Count
                testFormula = .Value
                If testFormula = vbNullString Then testFormula = 0
                With .FormatConditions(i)
                    If .Type = xlCellValue Then
                        Select Case .Operator
                            Case xlBetween
                                testFormula = "AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & ")"
                            Case xlNotBetween
                                testFormula = "NOT(AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & "))"
                            Case xlEqual
                                testFormula = testFormula & "=" & .Formula1
                            Case xlGreater
                                testFormula = testFormula & ">" & .Formula1
                            Case xlGreaterEqual
                                testFormula = testFormula & ">=" & .Formula1
                            Case xlLess
                                testFormula = testFormula & "<" & .Formula1
                            Case xlLessEqual
                                testFormula = testFormula & "<=" & .Formula1
                            Case xlNotEqual
                                testFormula = testFormula & "<>" & .Formula1
                        End Select
                    Else
                        tempFormula = Application.ConvertFormula(.Formula1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
                        testFormula = Application.ConvertFormula(tempFormula, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1, _
                                                ToAbsolute:=True, RelativeTo:=oneCell)
                    End If
                End With
                If Evaluate(testFormula) Then Exit For
            Next i
        End With
        CFormatMet = i Mod (oneCell.FormatConditions.Count + 1)
End Function

Module 2:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/16/2010 by Mike Erickson
'

'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="5"
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 23
End Sub

Sub makeCFDialog()
Application.Dialogs(xlDialogConditionalFormatting).Show
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/16/2010 by Mike Erickson
'

'
    Application.Run "CF.xls!makeCFDialog"
End Sub
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/16/2010 by Mike Erickson
'

'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="5"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
        .Underline = xlUnderlineStyleSingle
        .Strikethrough = True
        .ColorIndex = 9
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlDash
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Interior
        .ColorIndex = 46
        .PatternColorIndex = 33
        .Pattern = xlDown
    End With
End Sub
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 1/16/2010 by Mike Erickson
'

'
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

reports are mixed, some users claimed it worked others not...
Sorry not to be able to help you more.

 

[Rick Rothstein]

Conditional Formatting is not an easy thing to work with. Exactly what format or formats are the Conditional Formatting applying to your cells and what are the actual conditional relations themselves.

 

[sgg03246]

Hello Cyrilbrd,
Thanks so much for your help and sharing.

I've tried "CopyOnlyFormatCondionColorSAS", which i find straightforward, but indeed the format is not registered correctly may be due to format conditions (1) or higher number as suggested by Mickerisons' code.

I didn't investigate Mickerisons' code in detail, but the conditions format which i've only 2 colours to signifies TRUE to FALSE, were reversed when the codes were run.

Then i realised Rick's comments on the type of condition format that is used for the codes to work.

I'm appreciative for both your feedbacks and hints and direction on how i should continue investigate on appropriate codes to make it work. The search continues

Thanks again for all your help

 

[cyrilbrd]

pls keep us posted I would like to see what you'll find out.

 

[lchurch1]

This is a reply to an old post but this thread shows up in a google search on the first page on this subject and didn't appear to have come to a conclusion so I thought I'd throw in my 2 cents on a more simplistic method that I ended up using. This may not be usable in all situations, but if the formatting is fill related, toss on a filter, filter by color, then color the cells in the traditional fashion. You can then remove the conditional formatting and the traditional formatting you just did will stay. If the formatting goes beyond simple background fills then it gets a bit more complicated, but if you can replicate the conditional formatting conditions with a filter then it can be done with relative ease, depending on how many different conditions you're talking about and how the affected cells are laid out. If you've got 200 columns with conditional formats smattered everywhere this doesn't help much, that would take forever to do column by column.