Hello, I wonder if anyone can help...
I am looking to have 3 different conditional formats in one cell. I am using Mcirosoft Excel 2007.
1) Green if the cell that I am in is 95% of cell A1
2) Amber if the cell that I am in is within 80 - 94% of cell A1
3) Red if the cell that I am in is under 80% of cell A1
What is the formula I will need to use for the above?
Check this out. Go into manage rules to see formulae.
Last edited by Russell Dawson; 04-25-2012 at 11:48 AM. Reason: Wrong file uploaded
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
OK. I think that seems to work. The really annoying thing is when I copy the format of the cell and drag it across to the right, it changes the cells each time, and because I put in $ signs so that the first cell doesnt move, I then cant change the formula in the formatting because itll change it for all the cells I had copied it to.
Does that make sense, can anyone help?
Hi
Which is the range that you need to highlight?
Criteria, will be always the same? I mean $A$1, comparing with B1 & C1 & d1.....?
I think you have only to modify the $ sign.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hey,
So I have figured out the formattin bit but I dont think the formulas the the guy from befoer provided are correct.
Currently Ive got:
Red: G5<I5*80%
Green: G5>I5*94%
Amber: G5>I5*79%
But what I really want is:
If cell G5 is less that 80% of cell I5 make it Red if not
If cell G5 is within 80% to 94% of cell I5 make it Amber
If cell G5 is 95% or more than I5 make it Green
Does that make sense?
What about - Colour the cell Red anyway to start - If G5 is >= 95% of I5 go Green ( Stop if True ) , If G5 is >= 80% of I5 go Amber
So that gives me this:
Green=$G$6>95%*I6
Amber=$G$6>80%*$I$6
It still doesnt seem to work. I wonder wether its do with the order of these but that doesnt seem to make a different.
Ant suggestions?
The order is crucial. If you do it in the way I demonstrated in my example and substitute the cell references you have now chosen then it should work. You only specified A1 in your question.
It appears to work on mine - make sure you have the 95% green first with the "Stop if true" box ticked
I have attached an example and put the formatting in cell I6.
Still doesnt work for me.
Any ideas on the above>?
Right.
Try this. As BarryTSL says, there may be an issue with "Stop if true" box. It was written in V 2007 with V 2003 in mind of course so see how you go on with it. I've applied it to the full table as that seems logical and save you further grief
Good luck
Does this version work ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks