Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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? |
#2
![]() |
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I get the font color to change automatically depending on | Excel Discussion (Misc queries) | |||
Use DocProps in a change declaration | Excel Worksheet Functions | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
How to change the default font and size of "comments"? | Excel Discussion (Misc queries) | |||
Worksheet Row Change event | Excel Discussion (Misc queries) |