Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy the new Excel 2007 conditional formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy the new Excel 2007 conditional formats
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy the new Excel 2007 conditional formats
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 ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy the new Excel 2007 conditional formats
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 ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy the new Excel 2007 conditional formats
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 ... 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 ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Conditional formatting with number formats | Excel Discussion (Misc queries) | |||
Copy Conditional Formats on a List | Excel Discussion (Misc queries) | |||
Excel, how to copy conditional formats in sequential order 1,2,3 | Excel Worksheet Functions | |||
copying conditional formats (2007 Beta) | Excel Worksheet Functions | |||
Copy Conditional Formats | Excel Programming |