Results 1 to 2 of 2

remove conditional formatting but keep formatting

This is a discussion on remove conditional formatting but keep formatting within the Excel Questions forums, part of the Question Forums category; does anyone has a macro that can remove all conditional formating but keeping the curren format i have a spreadsheet ...

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    28

    Default remove conditional formatting but keep formatting

    does anyone has a macro that can remove all conditional formating but keeping the curren format
    i have a spreadsheet that contains about 60000 rows and i will like to be able to remove condition so i can sort by cell color
    any ideas suggestions i will apreciate
    i have this macro that some times works but most of the time doesnt work
    i found this macro on the internet so i dont know who wrote it
    Code:
    Sub NonConditionalFormatting(control As IRibbonControl)
    Dim cel As Range
    Dim boo As Boolean
    Dim frmla As String
    Dim i As Long
    Application.ScreenUpdating = False
    'For Each cel In ActiveSheet.UsedRange  'Remove conditional formatting from entire worksheet
    For Each cel In Selection   'Remove conditional formatting from selected cells
        If cel.FormatConditions.Count > 0 Then
            cel.Activate
            With cel.FormatConditions
                For i = 1 To .Count
                    frmla = .Item(i).Formula1
                    If Left(frmla, 1) = "=" Then
                        boo = Application.Evaluate(frmla)
                    Else
                        Select Case .Item(i).Operator
                        Case xlEqual  ' =
                            frmla = cel & "=" & .Item(i).Formula1
                        Case xlNotEqual
                            frmla = cel & "<>" & .Item(i).Formula1
                        Case xlBetween
                            frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                        Case xlNotBetween
                            frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                        Case xlLess
                            frmla = cel & "<" & .Item(i).Formula1
                        Case xlLessEqual
                            frmla = cel & "<=" & .Item(i).Formula1
                        Case xlGreater
                            frmla = cel & ">" & .Item(i).Formula1
                        Case xlGreaterEqual
                            frmla = cel & ">=" & .Item(i).Formula1
                        End Select
                        boo = Application.Evaluate(frmla)
                    End If
                    If boo Then
                        cel.Font.ColorIndex = .Item(i).Font.ColorIndex
                        cel.Interior.ColorIndex = .Item(i).Interior.ColorIndex
                        Exit For
                    End If
                Next i
                .Delete
            End With
        End If
    Next cel
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,621

    Default Re: remove conditional formatting but keep formatting

    Couldn't you just do a sort based on the conditions you are using for the formatting?

    That might need a helper column(s) but it would probably be far easier than using code.
    If posting code please use code tags.

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