remove conditional formatting but keep formatting

martinez_pedro

New Member
Joined
Aug 19, 2009
Messages
28
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top