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

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

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