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
|
Similar Threads
