Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing order of conditional formats
Hello,
I have a rather large range of cells that have 3 conditional formats. I'd like to change the order that the formats are applied. However, most of the cells also have other "unconditional" formats, so simply making the changes in one cell then copying/pasting formats would mean having to go back through and manually change those. So, I'm looking for a way to change the order of the formats in VBA. However, I'm getting stuck at, what should be, the very end. I've been able to move the formatconditions object to a variable (as near as I can tell, anyway), but then can't move the object from the variable back to the formatconditions object. I've tried the add method and the modify method, but both give me a runtime error. Below is snip of the code: ---------- Dim Wkbk As Workbook, Nme As String, rng As Range, Cl As Range Dim Valhldr(1 To 3) As Object Set Wkbk = ActiveWorkbook Nme = "testrange" Set rng = Wkbk.Names(Nme).RefersToRange For Each Cl In rng For i = 1 To 3 Set Valhldr(i) = Cl.FormatConditions(i) Next i For i = 1 To 3 Cl.FormatConditions(1).Delete Next i Cl.FormatConditions(1).Add = Valhldr(3) Cl.FormatConditions(2).Add = Valhldr(1) Cl.FormatConditions(3).Add = Valhldr(2) Next Cl -------------------- So, if anyone has any suggestions on how I can move the formats from the variable back to the formatconditions, or has another suggestion on how I can do this, I'd really appreciate it. The conditions themselves are rather complex formulas. Hardcoding them is an option, but figured I'd see if this could work. Thanks to all. -- Mike Lee McKinney,TX USA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing order of conditional formats
Why are you deleting and then adding. Whhy not just modify the values?
Dim Wkbk As Workbook, Nme As String, rng As Range, Cl As Range Dim Valhldr(1 To 3) As Object Set Wkbk = ActiveWorkbook Nme = "testrange" Set rng = Wkbk.Names(Nme).RefersToRange For Each Cl In rng Valhldr(1) = Cl.FormatConditions.formula1 Valhldrcolor(1) = Cl.FormatConditions.color.index Valhldr(2) = Cl.FormatConditions.formula2 Valhldrcolor(2) = Cl.FormatConditions.color.index Valhldr(3) = Cl.FormatConditions.formula3 Valhldrcolor(3) = Cl.FormatConditions.color.index Cl.FormatConditions(1).Modify xlCellValue, xlEqual, Valhldr(3) Cl.FormatConditions(1).interior.color.index = Valhldrcolor(3) Cl.FormatConditions(2).Modify xlCellValue, xlEqual, Valhldr(1) Cl.FormatConditions(2).interior.color.index = Valhldrcolor(1) Cl.FormatConditions(3).Modify xlCellValue, xlEqual, Valhldr(2) Cl.FormatConditions(3).interior.color.index = Valhldrcolor(2) Next Cl "mikelee101" wrote: Hello, I have a rather large range of cells that have 3 conditional formats. I'd like to change the order that the formats are applied. However, most of the cells also have other "unconditional" formats, so simply making the changes in one cell then copying/pasting formats would mean having to go back through and manually change those. So, I'm looking for a way to change the order of the formats in VBA. However, I'm getting stuck at, what should be, the very end. I've been able to move the formatconditions object to a variable (as near as I can tell, anyway), but then can't move the object from the variable back to the formatconditions object. I've tried the add method and the modify method, but both give me a runtime error. Below is snip of the code: ---------- Dim Wkbk As Workbook, Nme As String, rng As Range, Cl As Range Dim Valhldr(1 To 3) As Object Set Wkbk = ActiveWorkbook Nme = "testrange" Set rng = Wkbk.Names(Nme).RefersToRange For Each Cl In rng For i = 1 To 3 Set Valhldr(i) = Cl.FormatConditions(i) Next i For i = 1 To 3 Cl.FormatConditions(1).Delete Next i Cl.FormatConditions(1).Add = Valhldr(3) Cl.FormatConditions(2).Add = Valhldr(1) Cl.FormatConditions(3).Add = Valhldr(2) Next Cl -------------------- So, if anyone has any suggestions on how I can move the formats from the variable back to the formatconditions, or has another suggestion on how I can do this, I'd really appreciate it. The conditions themselves are rather complex formulas. Hardcoding them is an option, but figured I'd see if this could work. Thanks to all. -- Mike Lee McKinney,TX USA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing order of conditional formats
ahhh...i was trying to move the entire object instead of breaking it down
into it's individual components. thanks for the insight. -- Mike Lee McKinney,TX USA "Joel" wrote: Why are you deleting and then adding. Whhy not just modify the values? Dim Wkbk As Workbook, Nme As String, rng As Range, Cl As Range Dim Valhldr(1 To 3) As Object Set Wkbk = ActiveWorkbook Nme = "testrange" Set rng = Wkbk.Names(Nme).RefersToRange For Each Cl In rng Valhldr(1) = Cl.FormatConditions.formula1 Valhldrcolor(1) = Cl.FormatConditions.color.index Valhldr(2) = Cl.FormatConditions.formula2 Valhldrcolor(2) = Cl.FormatConditions.color.index Valhldr(3) = Cl.FormatConditions.formula3 Valhldrcolor(3) = Cl.FormatConditions.color.index Cl.FormatConditions(1).Modify xlCellValue, xlEqual, Valhldr(3) Cl.FormatConditions(1).interior.color.index = Valhldrcolor(3) Cl.FormatConditions(2).Modify xlCellValue, xlEqual, Valhldr(1) Cl.FormatConditions(2).interior.color.index = Valhldrcolor(1) Cl.FormatConditions(3).Modify xlCellValue, xlEqual, Valhldr(2) Cl.FormatConditions(3).interior.color.index = Valhldrcolor(2) Next Cl "mikelee101" wrote: Hello, I have a rather large range of cells that have 3 conditional formats. I'd like to change the order that the formats are applied. However, most of the cells also have other "unconditional" formats, so simply making the changes in one cell then copying/pasting formats would mean having to go back through and manually change those. So, I'm looking for a way to change the order of the formats in VBA. However, I'm getting stuck at, what should be, the very end. I've been able to move the formatconditions object to a variable (as near as I can tell, anyway), but then can't move the object from the variable back to the formatconditions object. I've tried the add method and the modify method, but both give me a runtime error. Below is snip of the code: ---------- Dim Wkbk As Workbook, Nme As String, rng As Range, Cl As Range Dim Valhldr(1 To 3) As Object Set Wkbk = ActiveWorkbook Nme = "testrange" Set rng = Wkbk.Names(Nme).RefersToRange For Each Cl In rng For i = 1 To 3 Set Valhldr(i) = Cl.FormatConditions(i) Next i For i = 1 To 3 Cl.FormatConditions(1).Delete Next i Cl.FormatConditions(1).Add = Valhldr(3) Cl.FormatConditions(2).Add = Valhldr(1) Cl.FormatConditions(3).Add = Valhldr(2) Next Cl -------------------- So, if anyone has any suggestions on how I can move the formats from the variable back to the formatconditions, or has another suggestion on how I can do this, I'd really appreciate it. The conditions themselves are rather complex formulas. Hardcoding them is an option, but figured I'd see if this could work. Thanks to all. -- Mike Lee McKinney,TX USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats, how to scroll and view all formats? | Excel Worksheet Functions | |||
How to change series plotting order without changing legend order? | Charts and Charting in Excel | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
How stop Excel file UK date order changing to US order in m.merge | Excel Discussion (Misc queries) | |||
Excel, how to copy conditional formats in sequential order 1,2,3 | Excel Worksheet Functions |