Excel 2007 Conditional Formatting
Excel has made numerous changes in its conditional formatting between 2003 and 2007. Most of the new features I think are great, though there are a lot of problems with backward compatibility.
One thing I discovered this week is a trap for the unwary.
In Excel 2003 it was easy to create a conditional format for a cell, and then copy that cell or even just the formatting for that cell to other cells, and the conditional formatting would be copied also. Of course it pays to make sure that you correctly apply absolute and relative cell references in the conditional formatting if using formulas. And using paste special or the format painter you could copy just the formatting from once cell to another - including the conditional formatting.
In Excel 2007 this works fine if you are copying to cells that don't already have conditional formatting in them., BUT if you are copying to cell with existing conditional formatting the paste actually appends the new rule(s) to the existing one(s). This can cause some very strange results to occur.
Example
For example if you want all the cells in column A to be coloured green if they are not blank - E.g., have something in them, then add a formula rule to cell A1 as follows. This part works for Excel 2003 as well as 2007.
- Click on the cell, Click on Conditional Formatting on the ribbon (2007) and then New Rule. Excel 2003, Click Format | Conditional Formatting
- Add a formula rule =A1<>"" and format the pattern or fill for Green.
- Click OK back to the worksheet and then enter something in cell A1 to test. The fill colour should change.
- Now delete the cell contents and then copy the cell and select the whole column (CTRL SpaceBar) and Paste.
- Now any cell that contains anything other than blank will be formatted as per the conditional format you chose.
Excel 2007 only.
Now if you were to create a conditional format in some other cell - eg Cell C1 and then copy that cell to cell A1, it wouldn't over write the conditional format in A1 but add to it. Thus there would be two rules in cell A1.
If you wanted to overwrite the format in A1 you would first need to clear it, Select Conditional formatting and Clear Rules from Selected Cells before copying and pasting from C1.
Some other references
Eric Patterson one of the members of the Excel Program Management team has written a number of articles on the Microsoft Excel Blog maintained by David Gainer. This is useful from a technical perspective, but doesn't really help much when dealing with large multi sheet spreadsheets with complex formulas and formatting applied. I will stick by my first impression of Excel 2007, treat it as a new application and don't try and make things compatible, or if compatibility is required use only 2003 features.
Eric writes on:
Conditional Formatting Compatibility
Deprecated features for Excel 2007
Microsoft White Paper: Migrating to Excel 2007
Note this point:
Files containing new conditional formatting can be edited with earlier versions of Excel. Users can change cell values, sort ranges, add formatting, and do a number of other tasks without disturbing the conditional formatting. In general if the user does not make changes directly to the conditional formatting on a range, it will safely reappear as designed when the workbook is opened again in Office Excel 2007.
Summary
Excel 2007 has a lot of really nice and easily applied conditional formatting features. However my advice is don't use them unless you know you will not need to use the file in a Excel 2003 environment. We use them in internal files here at Jethro, but for all client files where they require 2003 and 2007 compatibility we are restricted to using what Excel 2003 can handle. (This advice applies to all Excel 2007 only features).
Be careful when copying cells containing conditional formats on top of other cells with conditional formats - it will not overwrite the format as it did in Excel 2003.
Site Construction and Maintenance by Jethro Consultants
Gidday
Glad to have you stop in.
I write about my family, the internet, technology and gadgets, productivity with Microsoft Office applications Excel Tips and more.
Instagram Feed
Find Us On...
Subscribe by RSS
Recent comments
- ill try to resolve that
10 years 37 weeks ago - When I initially commented I
10 years 37 weeks ago - What I would really like to
10 years 39 weeks ago - sure @spyjournal
10 years 39 weeks ago - Howdy! Do you use Twitter?
10 years 39 weeks ago - You really make it appear
10 years 39 weeks ago - Oh my goodness! Awesome
10 years 39 weeks ago - I think you have to do it via
10 years 39 weeks ago - When I originally commented I
10 years 39 weeks ago - go to hell india
10 years 39 weeks ago
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Timothy Miller © unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission. Privacy Policy
Comments
Great site. I've had a
Great site. I've had a problem where I lose conditional formatting when saving other changes to a 2003 Excel file in a 2007 environment. This has happened twice. I'd love to prevent it, but I don't know what's triggering it. Any thoughts?
Kеeρ on wοrking, great jοb!
Kеeρ on wοrking, great jοb!
Hi Nikki and thanks for the
Hi Nikki and thanks for the comment
Without looking at the workbook I cant say for sure what is happening. However what I suspect is that the specific conditional format settings you have in the Office 2003 format are just not converting correctly when you save it as 2007. They are completely different in the way they manage the rules.
Feel free to email me the file if you want me to look at it.
I am trying to determine a
I am trying to determine a conditional formula.
In a cell (say H3) I can place a number (either 12, 24, 36) in cell H4 i place a fixed number 2400, in cell H5 i want to place a formula that will recognise and give a result if H3 is 12, but if h3 is 24 i want H5's result to be as a result of a slightly different formaula, a so on if H3 is 36.
Can you help please??
Sure Steve in cell H5 write a
Sure Steve
in cell H5 write a formula that says
=if(h3=12,x,if(h3=24,y,z))
where x is the result for when h3=12, y is the result for when h3=24 and z is the result for when h3=36
x,y and z can be formulas
hope that helps
Thanks, much appreciated!!!
Thanks, much appreciated!!!
I just upgraded to Excel 2007
I just upgraded to Excel 2007 - I used to consider myself a wizard at conditional formatting, but now none of my 'trade secrets' seem to work any more!
In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?
awesome question Stephen i
awesome question Stephen
i will get back to you soon with the exact answer and publish it on here also - right now i'm going to bed!
Cheers
The answer is posted in
The answer is posted in detail at http://www.spyjournal.biz/node/903
Hi Jethro, 2 problems i'm
Hi Jethro,
2 problems i'm having when running your example of copying A1 to the entire column.
1) All conditional format formulas reference A1, instead of each referencing themselves.
2) The scroll bar on the right becomes very very small... Hugely annoying. Is there any way to fix that??? I have the same problem on some other spreadsheets.
Thanks.
Hi Ben You may not be doing
Hi Ben
You may not be doing it just right.
Excel takes note of the last used cell in a file and expands the file size accordingly. If that is the cell A1048576 then Excel has to "remember" a bunch of cells. That is why the scroll bar on the right is small because it scales accordingly.
The way to get around this is to change whole columns rather than cell ranges with formatting requirements. This actually uses less memory as well.
So to make the instructions above work for you, Click on Column A - so the whole column is selected - in a fresh workbook.
Then go to the Conditional Formatting and New rule. Enter the formula exactly as =A1<>"" and chooses your fill colour with the format button.
Make sure you don't click on cell A1 as that will give you$A$1 which is an absolute reference. What you need is an relative reference.
In addition in Excel 2007, you will need to make sure that there are not other rules being applied that take precedence. Thats Why I suggest using a new workbook to learn this skill.
Hope that helps
Cheers
Go to the bottom and right of
Go to the bottom and right of the last item of data on your spreadsheet. From there select all remaining columns or all remaining rows as against the whole sheet. We wish to delete all possible data, formatting etc. Select ALT E,A,A which will clear the lot, accidental formatting etc. Then go CTRL HOME, save the file, close it and reopen it and you will find the scroll bar runs only the length, width of the data and not the whole spreadsheet. You'll also find the file is somewhat reduced in size too.
I am working on a spreadsheet
I am working on a spreadsheet where I am doing conditional formatting. Example: I have a value in A1 and a value in A5. I want A1 to have the conditional formatting comparing it to A5. I want to copy that conditional formula to B1 and have it compare to B5 not A5. And then C1 compared to C5. How do I make the conditional formatting a "series" if that is the correct term? I have tried EVERYTHING, I think and it keep comparing the first value only. A1 to A5 then B1 back to A5 then C1 back to A5. Is that understandable? Thank you!
Hi Jackie This will be the
Hi Jackie
This will be the same problems as others have had. You need to take the $ signs out of the reference to A5. Then it will become relative and not absolute.
If you need more details let me know.
Cheers
Tim
I have tried the above and it
I have tried the above and it is does not seem to work. If I am comparing cells A1-A10 to B1 to B10 and I have my Conditional Format set up for cell A1. Now I want to copy that conditional format to the remaining nine rows. So I edit the conditional format for cell A1 so it is ruled as "Cell Value <> B1". Notice there are no $ signs. For the Applies to field, it is set =$A$1 (cannot remove $ signs). Next I do a Copy then select the cells A2 through A10. I right-click Paste Special, select Formats and click OK. The result is that only the first cell A2 is set correctly in terms of the rule, but the Applies To field is set to $A$2-$A$10. The same is for the remaining cells in the column; they are ruled to cell B2 and applied to cells A2 to A10.
The question is therefore, how can we have the cell in the rule to be Bn, where n is the current row and the cell in the Applies To field be the cell where the Conditional Format is being applied and be able to select an entire column to do this instead of individually selecting one cell at a time.
thank you
good question I haven't
good question
I haven't specifically tested your exact problem - but i think i can guess whats going wrong. rather than trying to setup a range and copy to another range, set up one cell that will dynamically adjust to work with any range and then use that to copy.
Eg set $A1 and then copy that down. then copy across to B1 and edit it so it refers to $B1 and then copy that down. does that make sense? if not send me an example spreadsheet so i can see your exact problem and try and solve it for you.
I have the simplest of
I have the simplest of conditional formatting questions like the others on this blog. I want to compare to columns, lets say A and B. I want B to turn red when the date is less than A. I know how to set conditional formatting for each cell, but I need to copy them to about a hundred cells. I have many cells and columns to format and I don't have time to input each cell. When I do the special paste and I check the rules there is still a rule attached to the cell I originally copied. I could do this in my sleep in Excel 2003 and it worked great, why did they change it in 2007? Please tell me how to copy the formatting!
they changed the formatting
they changed the formatting when they brought out 2007 to improve its capabilities. the conditional formatting in excel2003 was very limited - maximum of 3 conditions, no ability to stop processing, no ability to add ranges, no icon sets or other special features.
so with improved functionality comes additional settings and more difficulty sometimes in applying complex formatting.
when you go into manage formatting select the formatting rule you are looking at and make it apply to a range. this is much easier now - you can visibly see the range it applies to unlike excel 2003 where you couldnt see it at all.
When I originally commented I
When I originally commented I clicked the "Notify me when new comments are added" checkbox and
now each time a comment is added I get three emails with
the same comment. Is there any way you can
remove people from that service? Many thanks!
I think you have to do it via
I think you have to do it via your user account
I have the simplest of
I have the simplest of conditional formatting questions like the others on this blog. I want to compare to columns, lets say A and B. I want B to turn red when the date is less than A. I know how to set conditional formatting for each cell, but I need to copy them to about a hundred cells. I have many cells and columns to format and I don't have time to input each cell. When I do the special paste and I check the rules there is still a rule attached to the cell I originally copied. I could do this in my sleep in Excel 2003 and it worked great, why did they change it in 2007? Please tell me how to copy the formatting!
Trying to copy a conditional
Trying to copy a conditional formatting formula from a cell down to other cells in the same column doesn't work. only the first destination cell row # is incremented the remaining rows don't increment. And, no I do not have a $ in front of the row designation in the cell to be copied. I use Excel2007??
i would need to see the
i would need to see the spreadsheet to see what you are doing wrong
can you send it to me?
Howdy! Do you use Twitter?
Howdy! Do you use Twitter? I'd like to follow you if that would be ok.
I'm definitely enjoying your blog and look forward to new posts.
sure @spyjournal
sure @spyjournal
Even worse is my next
Even worse is my next example: I have a sheet with conditional formats in for example cells A1 to A20. The conditions are in cells AA1 to AA20. Thus the conditional format in cell A1 says "=AA1", in cell A2 says "=AA2" and so on. If I open this sheet in Excel 2007 are as follows: if I open cell A1, it says "=AA1" and applies to "A1:A20", if I open cell A2, it says "=AA1" and applies to "A1:A20". I probably (a long time ago) created the conditions in cells A2:A29 by making the condition in cell A1 and copying the format to cells A2:A20. But this is very annoying and I would not know how to avoid it. My sheets have to remain compatible between xls2003 and xls2007!
the last sentence says it
the last sentence says it all
if you need compatibility between versions then do not create your conditional formats in excel 2007. It will not work.
open the file and create then in excel 2003
save as .xls (default for 2003)
when opening in 2007 keep it in compatibility format - that is do not convert it to an xlsx or xlsm format.
what you have there now will not work in 2003 i would surmise.
Hi Jethro I have labored over
Hi Jethro
I have labored over this same problem as hours. What you are saying is that I need to run both versions of Excel on my PC in order to create files that my 2003 colleagues (all 3,000 of them!) can use? Do you know if MS will try to fix this incompatibility problem or rely of the world upgrading to 2007 - great marketing strategy!
Hi Julia There are several
Hi Julia
There are several different things here that I can tell you to help you I hope.
The first is to reassure you there is no need to run both versions. If you have excel 2007 you can save files in 2003 mode. Excel 2007 will convert the files successfully for all compatible features and strip the non compatible features out. It will advise you of these in the process. If you are going to be regularly making files for 2003 users, then you can operate directly in 2003 mode. This will stop you from using features that are not compatible anyway.
The next thing is some information about the new version and why the files are different formats. Without going into the technicality of it, the previous versions of excel (and other office applications) made files that were in a binary format and proprietary to Microsoft. In an era of non trust, Microsoft locked up its intellectual property and made it difficult for other company's applications to work with their file formats. With office 2007 MS made a major shift away from the proprietary binary format to an open XML standard. This format is better for a whole lot of technical reasons as well as the fact that the format is is now readily available to other companies to see and use. This makes MS Office much more friendly. They have then enhanced security by requiring tighter trust requirements and a different file extension for macro enabled files helping reduce the chance of macro borne viruses.
Finally, they have also released free compatibility packs
Users with office 2003 can open office 2007 format files (downgrading them to the older version) by using the free compatibility applications.
So the marketing hasn't been the best at informing people - though all this information is readily available on the microsoft website. However if you want to compare to say a car or an camera or any other piece of technology where there are new versions produced that are better than old versions - there are always compatibility issues. You don't hear people complaining about how their wheels from their old ford don't fit on the new one. In this case I think microsoft not only have built some very awesome software with MS Office 2007,t hey have made it as easy as possible to convert, work backwards and so on. Of course its not perfect, and the conditional formatting is one of those places where it doesn't work well in both directions. However I am getting used to having the better functionality in Excel 2007 and definitely would not want to go back! I develop a lot of applications to be compatible in both versions for clients who need that dual compatibility.
Hope that answers your questions Julia.
Cheers
Tim
Thanks for your review of the
Thanks for your review of the Excel 2007 conditional formatting.
I would like to present here the tutorial vidoes I've made for self learning this subject, on the following address:
Excel conditional formatting
Thanks!
Hello all, First I'm not
Hello all,
First I'm not a programmar but I can find my way around by looking at posts like these and figuring out what I need for the most part. However, I can't find anywhere that can help me with copying excel format in 2007 to the column and not use the same reference. Below is what I'm shooting for and any help would be greatly appreciated:
In cell A1 = random date in future
In cell B1 = today()
cond format for B1 = Cell Value is > A1-60 then format cell yellow
cond format for B1 = Cell Value is > A1-30 then format cell orange
cond format for B1 = Cell Value is >= A1 then format cell red
I have it working now but the problem comes when I want to copy the conditional format to cell B2 through B100 to check A2 through A100 instead of all the copied conditional formating in B2 through B100 checking cell A1.
If I have to I'll go through each and every individual cell and input 3 conditional formating for every cell in column B, but there has to be an easier way then that to do it. Thanks in advance for any help that anyone can provide.
Again short version, I'm looking for a way to copy the conditional formating in cell B1 to all the B cells but instead of having all the B cells reference A1 have them each reference there respective rows A column.
Thanks,
Brandon
Please disregard the previous
Please disregard the previous post. My solution was as follows
=B2:B105>(A2:A105)-60 yellow
=B2:B105>(A2:A105)-30 orange
=B2:B105>=A2:A105 red
=A2:A105="NCR" blue
=A2:A105="AWC" purple
etc.
Thanks,
Brandon
Hi Brandon looks like you got
Hi Brandon
looks like you got it sorted OK. The thing to remember here is the relative and absolute references. the $ signs "lock" a column, row or both. So if you want to copy down and across but still reference the same column then lock the column and not the row eg $B4.
If you want to lock the row, the use B$4 for example. and to lock to a single cell for every time you copy it use $B$4.
Cheers
Relative v. Absolute
Relative v. Absolute referrencing.
I see the references to relative addressing and absolute addressing above. I did see one person state, as I have found, that in the specification of the block to which the conditional formatting is to be applied, relative addressing is not allowed. Whereas, were I to change from $A$1 to A1, as you recommend, the system reverts it back to $A$1 when the entry block loses focus. Is this a newly presented 'known limitation' to the system, whereby relative positioning of formatting is no longer possible, at least by the old methodology?
i havent found this a problem
i havent found this a problem - but then maybe i havent specifically tested your scenario. can you send me an example showing what you want and what excel is doing? email to tim at spyjournal dot biz
Tim
I am also having trouble with
I am also having trouble with relative vs. absolute addressing with conditional formatting. My problem, as I see it, is that there are actually two levels of relative vs. absolute addressing when dealing with a block. On one level, if you want the formatting for an entire block to be based on the value of one cell then you need to use absolute addressing. The problem with that is if you copy that block, it is going to still be looking back at the original one cell that was used to set up the formatting to begin with.
As an example, suppose that you want to color individual test grades green if they are above some arbitrary cut-off and red if they are below. If you wanted to be able to copy the formatting, you would either need to create a relative conditional format for each individual test grade separately, or you would need to change the absolute conditional formatting each time you copied the formatting to a new test.
Any ideas?
Hi Skip - that happens to be
Hi Skip - that happens to be my nickname also!
You are absolutely right. However I don't see this as a problem but actually a strength of 2007 vs 2003. In effect the result is the same. However in 2007 the visibility is much better. the conditional formatting dialog box shows you all the blocks and their settings in the sheet. the 2003 dialog doesn't show anything other than the relevant set of formats for the selected cell(s).
The concept is this (same in both versions)
If you create a single cell with conditional formatting that is relative and then copy it it is always relative.
If you create a single cell with an absolute reference and then copy it is always absolute. If you copy the selection in either case the result is the same. However in 207, you can set the range as a relative or absolute range. this allows you to control the specific area(s) that are managed by any set of rules.
The more you play with it the easier it becomes.
My suggestion is to work with only a few cells at a time and try and predict the expected result as you make changes.
Good luck
well worth the read. thank
well worth the read. thank you very much for taking the time to share with those who are starting to learn about excel conditional formatting. Greetings...
Surely one issue that can’t
Surely one issue that can’t be solved by being in 2003 mode in 2007 is if you extract into excel more than 64K lines. Does this mode allow for the nice new 2007 feature of allowing more lines?
If not you have to download the Compatibility pack for XL 2003 so you can see your conditional formatting as the saved in 2007 even if you do a ‘save as 2003’ from 2007?
?
I'm a bit confused to be
I'm a bit confused to be honest by your question
A 2003 file (.xls) cant have more than 65535 lines where as a .xlsx can. So you cant save as a file that large into 2003 mode anyway. If you are in 2007 you can see the new formatting, and if you choose not to apply it then it doesn't mater.
If you are in 2003, then you can not open a .xlsx file anyway so it doesn't matter then either.
Maybe you could explain what you are trying to do again and what doesn't work so I can answer more helpfully.
Your site is tremendous. I've
Your site is tremendous. I've looked long and hard for an answer to the following need. I want to copy selected rows of one worksheet to another worksheet (so I can observe the live data coming into these rows on the other worksheet). These rows have conditional formatting formula rules with relative references (to reflect row number). My understanding is that I need to change the rule formulas in the over 100 rows to contain absolute cell references. How can I make this change all at once to all the rows?
I'm afraid I have no idea
I'm afraid I have no idea what you are trying to do! try emailing me and maybe we can work it out
tim at spyjournal dot biz
HI Baffled in trying to get
HI
Baffled in trying to get to grips with conditional formating in 2007
Scenario :
Column A = Actual Result
Column B = Target
Column C = Needs to show
1) If Actual result is less than 90% of target turn font colour RED
2) If Actual result is >90% of target and is <100% of target turn font colour AMBER
3) If Actual result is >= 100% of target turn font colour GREEN
If column C meets criteria 1 use character "R", criteria 2 = "A" and criteria 3 = "G"
I can acheive 1 and 3 with =IF(a1b1,"G")) and then conditional format so that if contents of cell = R or G then colour font accordingly
I`m stumped with the % factor and also think there might be a better way of doing it
Any help would be appreciated :)
Hi, I have the same problem
Hi,
I have the same problem as a few people above but I don't think you got the point of the problem,
In xl2003 if you select A3:A9 and enter conditional format =B3 then select any other cell in the range it will show the conditional format for that cell e.g. A7 will show =B7
In xl2007 after following the same steps it will apply the correct formatting but if you select cell A7 to view the conditional format it will show as =B3. You get the same when checking any cell in range A3:A9.
This makes it near impossible to edit or amend or debug conditional formatting once entered.
Any advice would be appreciated.
Eric.
Hi Eric i understand the
Hi Eric
i understand the problem and the first answer is that you cannot apply the same sort of logic between the two versions. The new versions (including 2010) manage conditional formatting completely differently, though some of the same concepts apply.
First of all your conditional format formula should be =A3=B3. This will apply the conditional format when the cell in A3 contains the same value as the cell in B3, and this will be relative - eg. when A7=B7 the cell in A7 will have the format applied. So you can see that the relative approach is correct in the conditional format manager in Excel 2007/2010
Second the problem you mentioned. When you look at the relative formula in cell B7 in excel 2003 it showed you just that formula. that is actually harder to debug if you think about it in Excel 2003 than in 2007/2010 as there is no way to see what other cells it also applies to. In Excel 2007/2010 the conditional format manager allows you to see the formula - and the range it is applied to and the little range box (circled in the image in the article at http://www.spyjournal.biz/node/1229) allows you to view this
The manager allows you to confine the selection to be evaluated to the selected cell(s), the current worksheet or other worksheets in the same workbook. If you then understand the relative / absolute nature of the formula you created then you can debug very successfully. I actually preferred this once I got a hang of using the manager to create sets of conditional formats than can be applied to multiple ranges simultaneously.
I posted this as an article at http://www.spyjournal.biz/node/1229
Cheers
Tim
Hello, I'm sorry but I still
Hello, I'm sorry but I still don't get it. I have a range of values I want to compare to some targets, and show a red, yellow or green bubble if they are below/above the target or not.
Conditional formatting in Excel 2007 will NOT allow me to use relative reference, only absolute. As I have a few to compare, I don't want to create 400 rules but only one rule for the whole range, which compares A3 to B3, then adds a bubble to A3, then compares A4 to B4 (adds a bubble to A4), etc...
Can this be done? I've tried but no luck yet.
Thanks,
Santi
Hi Santi it absolutely does
Hi Santi
it absolutely does let you perform relative conditional formatting.
you just need to a create relative formula and then apply it to a range.
one rule for the entire range.
the instructions at this post should help you
http://www.spyjournal.biz/node/1229
cheers
Tim
Hi Tim, I am trying to work
Hi Tim, I am trying to work with the new Icon Sets from Excel 2007, and in those screens it does not allow to use relative reference. Please try one of those.
Conditional formatting > New Rule > Format Style = Icon Sets
Then if you put a relative reference in the Value field and Formula in the Type field, it brings back an error message that only absolute references can be used... which can be a pain in the neck for large spreadsheets... any clue how to solve this?
Thanks!
Santi
Thats right Santi Note the
Thats right Santi
Note the icon sets are new feature in 2007 and 2010. they dont mimic the previous conditional formatting style.
in fact trying to use them with a relative reference would negate the purpose of them.
The icon sets are designed to compare one of three values (absolute) and the relative result of each cell.
the way to set this up would be to use a cell that is an absolute reference that contains the value you want to test against and then set the orange to be equal to it, the green to be greater than it and the red to be lower than it - for example.
if you want to use the icon sets there is a way - it just takes an extra step
what you do is create a column that evaluates each cell and returns a 1,2 or 3 result depending on what you want to achieve. you can hide this column if you like. then you can use this column to create your icon set conditional formatting. i use this to display results based on complex dates and other conditions formulas - eg if something is older than today and another cell has an N in it then i make my cell 1. this becomes a red icon. if it is greater than today and has a Y in it it is a 3 - and that becomes green - etc.
hope this helps
Cheers
Tim
Hi Tim, thanks a lot for your
Hi Tim, thanks a lot for your fast replies... Yes I added that extra help column and I coded it in such a way that it can only contain -1 (red), 0 (yellow) or +1 (green), then the icon sets formulae will allow me to use those -1,0,1 as the decision factors behind painting the bubble one colour or another. Using white fonts and hiding columns will do the rest of the trick.
Thanks a lot and best regards from Barcelona.
Santi