Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Remove Conditional Formatting but keep format

This is a discussion on Remove Conditional Formatting but keep format within the Excel Questions forums, part of the Question Forums category; Hi there, Does anyone knows how i can remove Conditional Formatting but keep the format which has been done via ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default Remove Conditional Formatting but keep format

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

    Thanks.

  2. #2
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,694

    Default Re: Remove Conditional Formatting but keep format

    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
    V14.3.9

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default Re: Remove Conditional Formatting but keep format

    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.

  4. #4
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,694

    Default Re: Remove Conditional Formatting but keep format

    what version of excel are you using?
    V14.3.9

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default Re: Remove Conditional Formatting but keep format

    Excel 2010

  6. #6
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,694

    Default Re: Remove Conditional Formatting but keep format

    give it a try:

    Code:
    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:
    Code:
    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:
    Code:
    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.
    V14.3.9

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    13,230

    Default Re: Remove Conditional Formatting but keep format

    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  8. #8
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default Re: Remove Conditional Formatting but keep format

    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

  9. #9
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,694

    Default Re: Remove Conditional Formatting but keep format

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

  10. #10
    New Member
    Join Date
    Feb 2013
    Posts
    2

    Default Re: Remove Conditional Formatting but keep format

    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.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com