IS THERE A FAST WAY TO CHANGE THE ORDERS OF CONDITIONAL FORMATTIN.

[Mark_A_Cook]

I use conditional formatting in some of my spreadsheets and I noticed that if
the conditions are not in the right order, it doesnt work as well as I would
like. However, I don't know of a way to change the order without deleting
many of the conditions. This is quite troublesome since many of my
conditions have formulas and I have many conditions for the cells. Is there
a fast way to change the order of conditions, without deleating and re-typing
them?

[Rowan]

You can manipulate the conditional formatting with a macro. The example below
should get you started. This example will only work if each cell in selection
has same formatting (I think) and the only formatting it copies is cell
shading. Like I said, it could get you started. Also will only work if there
are 3 conditions.

Regards
Rowan

Sub SwapCond()

Dim Cond1 As Integer
Dim Cond2 As Integer
Dim Cond3 As Integer
Dim Op1 As Integer
Dim Op2 As Integer
Dim Op3 As Integer
Dim C1F1 As String
Dim C1F2 As String
Dim C2F1 As String
Dim C2F2 As String
Dim C3F1 As String
Dim C3F2 As String
Dim Col1 As Integer
Dim Col2 As Integer
Dim Col3 As Integer

On Error Resume Next
Cond1 = Selection.FormatConditions(1).Type
Cond2 = Selection.FormatConditions(2).Type
Cond3 = Selection.FormatConditions(3).Type
If Cond3 > 0 Then

Op1 = Selection.FormatConditions(1).Operator
Op2 = Selection.FormatConditions(2).Operator
Op3 = Selection.FormatConditions(3).Operator
C1F1 = Selection.FormatConditions(1).Formula1
C1F2 = Selection.FormatConditions(1).Formula2
C2F1 = Selection.FormatConditions(2).Formula1
C2F2 = Selection.FormatConditions(2).Formula2
C3F1 = Selection.FormatConditions(3).Formula1
C3F2 = Selection.FormatConditions(3).Formula2
Col1 = Selection.FormatConditions(1).Interior.ColorIndex
Col2 = Selection.FormatConditions(2).Interior.ColorIndex
Col3 = Selection.FormatConditions(3).Interior.ColorIndex

'Deletes conditions
Selection.FormatConditions.Delete
'Adds conditions back in new order.
'Cond 2 goes to 1, cond 1 to 3, 3 to 2
Selection.FormatConditions.Add Type:=Cond2, _
Operator:=Op2, Formula1:=C2F1, Formula2:=C2F2
Selection.FormatConditions(1).Interior.ColorIndex = Col2

Selection.FormatConditions.Add Type:=Cond3, _
Operator:=Op3, Formula1:=C3F1, Formula2:=C3F2
Selection.FormatConditions(2).Interior.ColorIndex = Col3

Selection.FormatConditions.Add Type:=Cond1, _
Operator:=Op1, Formula1:=C1F1, Formula2:=C1F2
Selection.FormatConditions(3).Interior.ColorIndex = Col1

End If

End Sub


"Mark_A_Cook" wrote:

> I use conditional formatting in some of my spreadsheets and I noticed that if
> the conditions are not in the right order, it doesnt work as well as I would
> like. However, I don't know of a way to change the order without deleting
> many of the conditions. This is quite troublesome since many of my
> conditions have formulas and I have many conditions for the cells. Is there
> a fast way to change the order of conditions, without deleating and re-typing
> them?