I have a cell that has been formatted using XL2007's fancy new conditional
formats (you know data bars, color scales, icon sets, etc) and I want to
copy those conditional formats to another cell in my code. I know this can
be done using Copy and PasteSpecial but this is to blunt an instrument
(i.e., Copy and PasteSpecial will transfer more properties from the source
to the target than I want). All I want is to copy the conditional formats.

It looks like the conditional format lives in the FormatConditions
collection of the range object but I'm not sure how to copy them.

For example, Target.FormatConditions = Source.FormatConditions doesn't work.

I can loop through them:

For Each condition in Source.FormatConditions
' what do I do here? Maybe ...
Target.FormatConditions.Add condition.????
' or maybe?
Target.FormatConditions.AddIconSetCondition condition.???
Next condition

Yikes.

TIA,

josh

I know, I'm late to the party and not going to be much fun while here.

But start by declaring your condition variable as type FormatCondition:

Dim condition As FormatCondition

Then in your loop you can examine the various properties of a
FormatCondition such as
..Application
..AppliesTo
..Borders
..Creator
..DateOperator
..Font
..Formula1
..Formula2
..NumberFormat
..Interior
..Operator
etc.

You will have to either Add or Modify the conditional formatting in your
target cell and set things up the way you want. See FormatConditions in
Help. There is also a new ConditionValue object that deals with data bar
conditional formatting.

"Josh Sale" wrote:

> I have a cell that has been formatted using XL2007's fancy new conditional
> formats (you know data bars, color scales, icon sets, etc) and I want to
> copy those conditional formats to another cell in my code. I know this can
> be done using Copy and PasteSpecial but this is to blunt an instrument
> (i.e., Copy and PasteSpecial will transfer more properties from the source
> to the target than I want). All I want is to copy the conditional formats.
>
> It looks like the conditional format lives in the FormatConditions
> collection of the range object but I'm not sure how to copy them.
>
> For example, Target.FormatConditions = Source.FormatConditions doesn't work.
>
> I can loop through them:
>
> For Each condition in Source.FormatConditions
> ' what do I do here? Maybe ...
> Target.FormatConditions.Add condition.????
> ' or maybe?
> Target.FormatConditions.AddIconSetCondition condition.???
> Next condition
>
> Yikes.
>
> TIA,
>
> josh
>
>
>
>

Hi "J",

Thanks for responding!!

I appreciate your comments. But I'm still left with some questions:

For example, there are a bunch of flavors of the Add condition method.
E.g., Add, AddAboveAverage, AddColorScale, AddDataBar, AddIconSetCondition,
etc. I don't know in advance which conditional format the user has applied.
When I look at the available properties of my Source range, I don't even see
what property tells me what kind of conditional format each "condition" my
loop returns so that I can I perform the correct flavor of add in my target
range (much less which source properties can be used to format the arguments
to whatever flavor of add I ultimately call).

Does my question make sense?

Any additional guidance?

josh



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
newsFA76432-0199-4AD5-9C19-(E-Mail Removed)...
>I know, I'm late to the party and not going to be much fun while here.
>
> But start by declaring your condition variable as type FormatCondition:
>
> Dim condition As FormatCondition
>
> Then in your loop you can examine the various properties of a
> FormatCondition such as
> .Application
> .AppliesTo
> .Borders
> .Creator
> .DateOperator
> .Font
> .Formula1
> .Formula2
> .NumberFormat
> .Interior
> .Operator
> etc.
>
> You will have to either Add or Modify the conditional formatting in your
> target cell and set things up the way you want. See FormatConditions in
> Help. There is also a new ConditionValue object that deals with data bar
> conditional formatting.
>
> "Josh Sale" wrote:
>
>> I have a cell that has been formatted using XL2007's fancy new
>> conditional
>> formats (you know data bars, color scales, icon sets, etc) and I want to
>> copy those conditional formats to another cell in my code. I know this
>> can
>> be done using Copy and PasteSpecial but this is to blunt an instrument
>> (i.e., Copy and PasteSpecial will transfer more properties from the
>> source
>> to the target than I want). All I want is to copy the conditional
>> formats.
>>
>> It looks like the conditional format lives in the FormatConditions
>> collection of the range object but I'm not sure how to copy them.
>>
>> For example, Target.FormatConditions = Source.FormatConditions doesn't
>> work.
>>
>> I can loop through them:
>>
>> For Each condition in Source.FormatConditions
>> ' what do I do here? Maybe ...
>> Target.FormatConditions.Add condition.????
>> ' or maybe?
>> Target.FormatConditions.AddIconSetCondition condition.???
>> Next condition
>>
>> Yikes.
>>
>> TIA,
>>
>> josh
>>
>>
>>
>>

Josh,
Like I said, I'm not going to be much fun while here.

Your questions would seem to make perfect sense, and I just don't have the
answers. I have not done any coding or poking around in this area at all
except for what little I did to come up with the teaser answer I put up
earlier. You might start by recording some macros to see what things are set
when you apply various types of conditional formatting to a cell and use the
resulting code from those macros as a roadmap to help you find our more about
it all or which ones you should be testing for. Like you, at the moment I'm
not even certain of how to determine how many conditions are set up. It may
be a count of the .FormatCondition for a given cell -- not sure if that's a
collection (.FormatConditions.Count ??) or not right at this moment.

It's kind of an interesting niche to explore, but that's where I'd have to
start right now - in the exploration stages. Wish I'd been more help, but
this is just a totally new area for Excel and I just don't know how it all
fits together at the moment.

"Josh Sale" wrote:

> Hi "J",
>
> Thanks for responding!!
>
> I appreciate your comments. But I'm still left with some questions:
>
> For example, there are a bunch of flavors of the Add condition method.
> E.g., Add, AddAboveAverage, AddColorScale, AddDataBar, AddIconSetCondition,
> etc. I don't know in advance which conditional format the user has applied.
> When I look at the available properties of my Source range, I don't even see
> what property tells me what kind of conditional format each "condition" my
> loop returns so that I can I perform the correct flavor of add in my target
> range (much less which source properties can be used to format the arguments
> to whatever flavor of add I ultimately call).
>
> Does my question make sense?
>
> Any additional guidance?
>
> josh
>
>
>
> "JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
> newsFA76432-0199-4AD5-9C19-(E-Mail Removed)...
> >I know, I'm late to the party and not going to be much fun while here.
> >
> > But start by declaring your condition variable as type FormatCondition:
> >
> > Dim condition As FormatCondition
> >
> > Then in your loop you can examine the various properties of a
> > FormatCondition such as
> > .Application
> > .AppliesTo
> > .Borders
> > .Creator
> > .DateOperator
> > .Font
> > .Formula1
> > .Formula2
> > .NumberFormat
> > .Interior
> > .Operator
> > etc.
> >
> > You will have to either Add or Modify the conditional formatting in your
> > target cell and set things up the way you want. See FormatConditions in
> > Help. There is also a new ConditionValue object that deals with data bar
> > conditional formatting.
> >
> > "Josh Sale" wrote:
> >
> >> I have a cell that has been formatted using XL2007's fancy new
> >> conditional
> >> formats (you know data bars, color scales, icon sets, etc) and I want to
> >> copy those conditional formats to another cell in my code. I know this
> >> can
> >> be done using Copy and PasteSpecial but this is to blunt an instrument
> >> (i.e., Copy and PasteSpecial will transfer more properties from the
> >> source
> >> to the target than I want). All I want is to copy the conditional
> >> formats.
> >>
> >> It looks like the conditional format lives in the FormatConditions
> >> collection of the range object but I'm not sure how to copy them.
> >>
> >> For example, Target.FormatConditions = Source.FormatConditions doesn't
> >> work.
> >>
> >> I can loop through them:
> >>
> >> For Each condition in Source.FormatConditions
> >> ' what do I do here? Maybe ...
> >> Target.FormatConditions.Add condition.????
> >> ' or maybe?
> >> Target.FormatConditions.AddIconSetCondition condition.???
> >> Next condition
> >>
> >> Yikes.
> >>
> >> TIA,
> >>
> >> josh
> >>
> >>
> >>
> >>
>
>
>

Sigh ...

Thanks. I had already recorded some macros and unforunately didn't get too
much value out of the exercise. Maybe I'll try again.

I see that John Walkenbach and others have updated their books for Excel
2007. I wonder if they are just warmed over versions of older books or if
in fact they've taken the time to explore this new corner of Excel's object
model?

josh


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
news:E2BA7CCB-30BF-4845-BF3B-(E-Mail Removed)...
> Josh,
> Like I said, I'm not going to be much fun while here.
>
> Your questions would seem to make perfect sense, and I just don't have the
> answers. I have not done any coding or poking around in this area at all
> except for what little I did to come up with the teaser answer I put up
> earlier. You might start by recording some macros to see what things are
> set
> when you apply various types of conditional formatting to a cell and use
> the
> resulting code from those macros as a roadmap to help you find our more
> about
> it all or which ones you should be testing for. Like you, at the moment
> I'm
> not even certain of how to determine how many conditions are set up. It
> may
> be a count of the .FormatCondition for a given cell -- not sure if that's
> a
> collection (.FormatConditions.Count ??) or not right at this moment.
>
> It's kind of an interesting niche to explore, but that's where I'd have to
> start right now - in the exploration stages. Wish I'd been more help, but
> this is just a totally new area for Excel and I just don't know how it all
> fits together at the moment.
>
> "Josh Sale" wrote:
>
>> Hi "J",
>>
>> Thanks for responding!!
>>
>> I appreciate your comments. But I'm still left with some questions:
>>
>> For example, there are a bunch of flavors of the Add condition method.
>> E.g., Add, AddAboveAverage, AddColorScale, AddDataBar,
>> AddIconSetCondition,
>> etc. I don't know in advance which conditional format the user has
>> applied.
>> When I look at the available properties of my Source range, I don't even
>> see
>> what property tells me what kind of conditional format each "condition"
>> my
>> loop returns so that I can I perform the correct flavor of add in my
>> target
>> range (much less which source properties can be used to format the
>> arguments
>> to whatever flavor of add I ultimately call).
>>
>> Does my question make sense?
>>
>> Any additional guidance?
>>
>> josh
>>
>>
>>
>> "JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
>> newsFA76432-0199-4AD5-9C19-(E-Mail Removed)...
>> >I know, I'm late to the party and not going to be much fun while here.
>> >
>> > But start by declaring your condition variable as type FormatCondition:
>> >
>> > Dim condition As FormatCondition
>> >
>> > Then in your loop you can examine the various properties of a
>> > FormatCondition such as
>> > .Application
>> > .AppliesTo
>> > .Borders
>> > .Creator
>> > .DateOperator
>> > .Font
>> > .Formula1
>> > .Formula2
>> > .NumberFormat
>> > .Interior
>> > .Operator
>> > etc.
>> >
>> > You will have to either Add or Modify the conditional formatting in
>> > your
>> > target cell and set things up the way you want. See FormatConditions
>> > in
>> > Help. There is also a new ConditionValue object that deals with data
>> > bar
>> > conditional formatting.
>> >
>> > "Josh Sale" wrote:
>> >
>> >> I have a cell that has been formatted using XL2007's fancy new
>> >> conditional
>> >> formats (you know data bars, color scales, icon sets, etc) and I want
>> >> to
>> >> copy those conditional formats to another cell in my code. I know
>> >> this
>> >> can
>> >> be done using Copy and PasteSpecial but this is to blunt an instrument
>> >> (i.e., Copy and PasteSpecial will transfer more properties from the
>> >> source
>> >> to the target than I want). All I want is to copy the conditional
>> >> formats.
>> >>
>> >> It looks like the conditional format lives in the FormatConditions
>> >> collection of the range object but I'm not sure how to copy them.
>> >>
>> >> For example, Target.FormatConditions = Source.FormatConditions doesn't
>> >> work.
>> >>
>> >> I can loop through them:
>> >>
>> >> For Each condition in Source.FormatConditions
>> >> ' what do I do here? Maybe ...
>> >> Target.FormatConditions.Add condition.????
>> >> ' or maybe?
>> >> Target.FormatConditions.AddIconSetCondition condition.???
>> >> Next condition
>> >>
>> >> Yikes.
>> >>
>> >> TIA,
>> >>
>> >> josh
>> >>
>> >>
>> >>
>> >>
>>
>>
>>