PDA

View Full Version : Solved: Dynamic Conditional Formatting



xluser2007
06-05-2008, 05:09 AM
Hi All,

I've come across a problem of trying to use the power of conditional formatting (CF) to cheange how cells are represented based on certain conditions, only to find the its (i.e. CFs) limted when the underlying formatting conditions change.

As such I was wondering how using VBA, or otherwise, how to make Conditional formatting dynamic with cell conditions. This is my way of trying to improve spreadsheet design and clarity.

I've attached a sample workbook. In the "Conditional_Format" Tab, I've defined 2 differently CF cells in A1 and A3.

I've then pasted the formats in an ad hoc fashion across columns C, E, and G and also the same in "Sheet2" Columns E, G and I.

Now effectively if the CF are based on A1 and A3 in the "Conditional_Format" tab, I would like to ideally like to only change the CFs (or condtions) in cells A1 and A3 to have the relevant pasted formats in cells in columns columns C, E, and G adjust automatically as well and also the same in "Sheet2" Columns E, G and I.

That is, the flow through change should be throughout teh workbook for similarly formatted cells, not just for the worksheet.

Is this possible easily either using VBA or without VBA?

VBAX experts, please shed some light on this problem.

regards

xluser2007
06-05-2008, 05:11 AM
Sample workbook attached.

Bob Phillips
06-05-2008, 05:50 AM
The problem is identifying an event to fire the code off, but I ahve to ask. Why?

xluser2007
06-05-2008, 06:00 AM
The problem is identifying an event to fire the code off, but I ahve to ask. Why?
Hi Bob,

I guess I'm just experimenting with optimal spreadsheet design (in the sense where end user has to spend minimal time updating the spreadsheet once set up).

To be more specific, many times at work we look at changes in our forecast numbers and manually highlight based on certain tolerance conditions.

So the next logical step was to use conditional formatting and paste across to automate the manual highlighting process.

The next step I thought was basically my question, setting only a handful of conditional formats. Then pasting the relevant CF on relevant cells. Then if your tolerance conditions change, they only need to be changed for 2 cells and the rest of them will flow through as desired.

Hope this helps to explain my motivation.

I am open to all suggestions. It would be nice to see how "easily" this can be implemented though for my curiosity's sake.

Bob Phillips
06-05-2008, 06:26 AM
Can we do it without CF in A1, A3, just cell colour?

xluser2007
06-05-2008, 06:34 AM
Can we do it without CF in A1, A3, just cell colour?

Bob, not trying to corner into a solution, but I reckon CF is the best way to go. The only reason I say that is most people are familiar with it and updates will mostlikely be easier going forward (even if VBA is applied to a CF condition).

And plus if the users want to make the change it is probably easier to do it via a 2 cells that are CF'd.

However, as I said I'm really open, the above was a relfelxive thought, but not necessarily the best one.

thanks for your help/ interest.

Bob Phillips
06-05-2008, 06:42 AM
It's okay, I was mis-understanding the layout of the spreadsheet.

My question should be, are you looking to change the conditions in A1 and A3 and then update any cells that have the same colour, or to change the colour of A1 and A3 and change the colour of the cells that have the same original colour and same condition?

xluser2007
06-05-2008, 06:48 AM
It's okay, I was mis-understanding the layout of the spreadsheet.

My question should be, are you looking to change the conditions in A1 and A3 and then update any cells that have the same colour, or to change the colour of A1 and A3 and change the colour of the cells that have the same original colour and same condition?
If I understand your question correctly Bob, then its both.

That is say I wanted to change the colour for any one of the conditions in A1,or I wanted to change the actual conditional formula, or I wanted to change both at the same time.

As such, the cells that have the FORMAT pasted from Cell A1 should dynamically change to adapt to the revised A1 format, both in terms of colour and/ or formula depending on what the user has changed.

Also, Bob, it's midnight here, and although I really want to stay and discuss further, I need catch some sleep :). I just wanted to let you know in case you didn't hear from me for a a while today.

Again thanks for your help and interest, I will respond ASAP tomorrow.

dalea
06-05-2008, 10:13 AM
Where you are limiting the power of CF is that you are putting the conditions you want to test to change the format into the formula as absolute values. Example, your code for cell A3 is "AND(A3>0,A3<=200)".

Assume that the "0" and "200" were Temperatures. Use the range name function of Excel to name a constant. For example name one constant "LowerLimit" and set it's value to "0" name another constant as "UpperLimit" and set it's value to "200".

Then change your CF formula in any cell you want to perform this test on to the following: "=AND(A3>LowerLimit,A3<=UpperLimit)". Now any time you want to change the tolerances you just use the Name Range function of Excel to change the values for "UpperLimit" and "LowerLimit".

I will often put these "what if" values in a table and name the cells that hold the values as above so I don't have to use the Range Name function in Excel but merely change the value in the small table of named ranges.

xluser2007
06-05-2008, 04:34 PM
Where you are limiting the power of CF is that you are putting the conditions you want to test to change the format into the formula as absolute values. Example, your code for cell A3 is "AND(A3>0,A3<=200)".

Assume that the "0" and "200" were Temperatures. Use the range name function of Excel to name a constant. For example name one constant "LowerLimit" and set it's value to "0" name another constant as "UpperLimit" and set it's value to "200".

Then change your CF formula in any cell you want to perform this test on to the following: "=AND(A3>LowerLimit,A3<=UpperLimit)". Now any time you want to change the tolerances you just use the Name Range function of Excel to change the values for "UpperLimit" and "LowerLimit".

I will often put these "what if" values in a table and name the cells that hold the values as above so I don't have to use the Range Name function in Excel but merely change the value in the small table of named ranges.
Dalea, very interesting suggestion. I like it.

Two thoughts though:

Firstly:

the conditions may chnage completely e.g. from =AND(A3>LowerLimit,A3<=UpperLimit)" to "=A1>2000" i.e. change the existing condition to be completely different, not juts a simple change in the upper bound of the existing constraint.

In this case I could still define teh formual as a string in another cell and then apply an INDIRECT function on the conditional formatting constarnt. Does this sound reasonable to you? I'll give this a go and report back.

Secondly:

How do you change the colour/ pattern/ effect of a cell for the relevant condition? The named range concept will work for resetting the condition, but not, I believe the changing colour/ pattern or effects that CF allows you to vary.

Do you concur, or have any workarounds for the second thought?

Also, Bob, I'm back now, if you ahve a spare moment to look into this, could you also please let me know your thoughts on the above.

regards,

Bob Phillips
06-05-2008, 04:45 PM
I was going along similar lines to Dalea, although I was thinking of defining an Excel name with the condition.

For instance, select A1 on the first sheet, create a name (Ctrl-F3) called say myTest, and a Refersto value of =!A1>100.

Then select all cells that the CF is applicable to and setup a condition of =myTest, withe the format.

Then to change the condition, just edit the name and change it to say =AND(!B9>100,!B9<2000). All the CFs then pick up this new condition. BTW I am assuming that B9 was the activecell when you edit, hence my use of B9.

Still have the colour problem, and that is where my CFConditions paper wil come in handy, I need to revisit that as the code is not going to be simple.

xluser2007
06-05-2008, 05:30 PM
I was going along similar lines to Dalea, although I was thinking of defining an Excel name with the condition.

For instance, select A1 on the first sheet, create a name (Ctrl-F3) called say myTest, and a Refersto value of =!A1>100.

Then select all cells that the CF is applicable to and setup a condition of =myTest, withe the format.

Then to change the condition, just edit the name and change it to say =AND(!B9>100,!B9<2000). All the CFs then pick up this new condition. BTW I am assuming that B9 was the activecell when you edit, hence my use of B9.
This is very nice Bob :)! I donl't know why I went to the option of of this using INDIRECT as it is far less transparent. The power of defined names is evident here. So the first question is fully answered.


Still have the colour problem, and that is where my CFConditions paper wil come in handy, I need to revisit that as the code is not going to be simple.
Yeah this does look tricky.

Bob, there is very little documentation on this topic online, but are Custom Styles what I'm really after?

That is coding a style and applying it using VBA or otherwise?

Thanks again for your valued input.

Bob Phillips
06-06-2008, 12:57 AM
Styles are somthing I am just getting into and I did consider it, but I dismissed it as it doesn't seem to carry the CF with the style, shame.

xluser2007
06-06-2008, 01:23 AM
Styles are somthing I am just getting into and I did consider it, but I dismissed it as it doesn't seem to carry the CF with the style, shame.
Ah, I see.

Bob, I'm not sure how to go about coding this, but here is a rough intuitive approach.

You select any Style for formatting, apply selected Style to to the relevant cells. The Style just acts as a marker for the relevant cells.

Different styles are used for different cells depending on how you you want to Conditionally format them.

Then you usie VBA (event driven code or otherwise) to CF on top of these styles.

I don't know if the CF and style will conflict, but we esentially want some sort of marker per CF cell, and the Styles might do it, or maybe not.

Just throwing more ideas on the table.

regards

Bob Phillips
06-06-2008, 01:46 AM
I don't think so because the style doesn't carry the CF metdata with it.

My thoughts, and I admit it is clunky, was along these lines. Have a dialog whereby the user designates the base cell. We pick up the CF colour from that cell. The user then uses a colour dialog to pick a new colour (we could even change the condition here?), and we go through each cell and check if it has the same CF colour as our base cell had, if so we update the CF to the new colour.

Two problems that I envisage. It might be horrendously slow on a gib workbook. It might reset the CF colour of a cell that is not linked to our base (or is that what you mean by using Style to identify the cells?).

Bob Phillips
06-06-2008, 05:52 AM
Wotcha think?

xluser2007
06-06-2008, 11:27 PM
Bob,

This is amazing :clap:!!!

I don't know how you keep making it seem so effortless. Thank you for your help in teaching myself and others all these interesting methods :thumb.

I've tried playing with it now for a while. Care to explain how it exactly works, you seem to be overlaying the relevant range with textbox for some reason. I'm keen to hear how you;ve gone about doing this.

Also, although you've solved the issue I had a promising alternative thought yesterday and I'm keen to hear your Insights on it:

Basically for each CF'd cell A1 with CF called CF1 you select the relevant cells to apply CF1 to and apply a named range to them.

That is for cells with CF1 to be applied you call the named range "CF1range". So when A1, which is CF1 formatted changes you just put through in workbook-level event code that says:

For each cell in Thisworkbook named range "CF1range"
Paste CF of Cell A1
Next cell

So all the user has to do is define the CF conditions in A1 (and A2, A3 etc),then have a userform determine the relevant named ranges to apply each condition to.

I think this will be quite easy for the user to update as well, as the Userform will do the work of updating the named ranges and the full power of CF can be used without reverse engineering it in any way, you will only need to change the formats yourself in A1, A2 and A3.

Please let me know what you think of this approach?

Thanks again Bob for your great efforts and patience on this one, appreciate it.

Bob Phillips
06-07-2008, 01:30 AM
Sorry I don't know your name so I can't address you properly, but here goes.

I picked up your idea of a range and whilst it will work, I do not think it is flexible. If you want to add a cell to the range you have to update the name, a non-trivial task.

What I did was to pick up on another idea that you had (at least I think this was what you were suggesting). I created a style called myStyle and applied it to cell A1. The great thing about that is if you copy cell A1 and paste it to any other cell, the CF goes with that copy, SO DOES THE STYLE. So when I run CFUpdate, the user nominates the source range, picks up the current CF colour, and also the style name. The user also picks the replacement colour. It then goes through all the cells in the usedrange on every sheet to see if the cell has that style, if so it updates the CF colour (don't forget the source cell will have that style, so it too gets updated).

Admittedly, I have to check every cell, whereas with a named range you wouldn't, but the flexibility of just copying A1 to another cell rather than updating the name justifies this IMO. Also, don't forget that named ranges cannot span worksheets, style usage can.

Also, no worksheet/workbook event is triggered by a CF change, which is why I drive it from a button in this example.

I think this is a great utility, not so much for the coding, that is pretty straightforward, but its concept. Some of that was my idea, but the original idea was yours. And I attrribute the way that I am using styles to you, if you didn't say/think that I read it that way, so you generated the idea. The ability to extend dependent CF cells so easily, to update the formula and have them all reflect that update, to change the colour, great stuff.

Of course, it needs some polishing. What if we have 2/3 conditions, but the basis is there.

iosi

xluser2007
06-09-2008, 07:36 AM
Sorry I don't know your name so I can't address you properly, but here goes.

I picked up your idea of a range and whilst it will work, I do not think it is flexible. If you want to add a cell to the range you have to update the name, a non-trivial task.

What I did was to pick up on another idea that you had (at least I think this was what you were suggesting). I created a style called myStyle and applied it to cell A1. The great thing about that is if you copy cell A1 and paste it to any other cell, the CF goes with that copy, SO DOES THE STYLE. So when I run CFUpdate, the user nominates the source range, picks up the current CF colour, and also the style name. The user also picks the replacement colour. It then goes through all the cells in the usedrange on every sheet to see if the cell has that style, if so it updates the CF colour (don't forget the source cell will have that style, so it too gets updated).

Admittedly, I have to check every cell, whereas with a named range you wouldn't, but the flexibility of just copying A1 to another cell rather than updating the name justifies this IMO. Also, don't forget that named ranges cannot span worksheets, style usage can.

Also, no worksheet/workbook event is triggered by a CF change, which is why I drive it from a button in this example.

I think this is a great utility, not so much for the coding, that is pretty straightforward, but its concept. Some of that was my idea, but the original idea was yours. And I attrribute the way that I am usingh styles to you, if if you didn't say/think that, I read it that way, so you generated the idea. The ability to extend dependent CF cells so easily, to update the formula and have them all reflect that update, to change the colour, great stuff.

Of course, it needs some polishing. What if we have 2/3 conditions, but the basis is there.
Apologies for my delayed reply, its a long weekend Down Under (thanks to your Queen :thumb) and thus couldn't respond to this properly.

Mate, I feel you are being too gracious. Yes, I did imply for the styles to be utilised in this manner, but it is a manner to actually produce a fully functioning utility. I hope that I can code this well, that quickly in the near future.

I felt that this was a really fun problem to begin with, but better yet was actually coming on VBAX and discussing with experts such as yourself who are so willing to throw ideas around and accept them and build on them considerably, from novices such as myself.

I'm going to experiment with this utility a lot more, as you said it has a lot of promise. I sure hope a great KB entry or addin can be produced out of it for others benefit as well.

I will experiment and report back and hope to gradually develop this interesting concept further.

Thanks again for your help, sincerely appreciate it :friends:.

regards,

Bob Phillips
06-09-2008, 07:56 AM
I agree with you, it was fun and there is a useful technique there that can vbe deployed elsehwere as well as a working tool.

I will leave it to you to develop it and write the KB entry.