How to copy the new Excel 2007 conditional formats

[Josh Sale]

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

 

[JLatham]

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]

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]

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]

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