I am trying to change row colours depending of data from 2 cells - I have included an example attached basically I want
a row to change to red 5 days prior to an expiry date
a row to change to light green if the item is open
a row to change to grey if it is Cancelled or Closed
Hi and welcome to the forum
you pretty much had what you wanted, you just need to tweek it a little...
highlight the entire range (in your sample, =$A$2:$N$2)
for the 1st rule...=$L2-TODAY()<5
for the 2nd rule..=$M2="Open"
for the 3rd rule..=$M2="Cancelled"
1 thing you need to decide though, is which rule takes precidence. IE which highlight do you want it the date is within 5 days AND M = Cancelled? color red or grey?
you can determine this by which rule you put 1st, and (i think 2003 has this?) by selecting to "stop if this rule is met" or not
edit: just checked, 2003 does not have "stop if true" option
Last edited by FDibbins; 02-03-2013 at 01:35 AM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I appreciate all your help so much Thank you - however I still cant get the cell to change to grey if closed despite what the date is! I have included the xample below and also the conditional formtting I have done - Also if possible would like the whole row (5) to change colour. - If I open this spreadsheet in a later version of Excel and tick the box will it convert over when I re-open on this old versin of excel?
OK, I tested this in 2000 and it works fine. All the CF's use "formula is", not "cell value is"...
1. highlight the entire range you want to change color.
2. for the 1st rule, use this =OR($M2="Cancelled",$M2="Closed")
3. for the 2nd rule use this =$M2="Open"
4. for the 3rd rule use this =($K2-TODAY())<5
Make sure you put the $ exactly where I had them, that fixes the column to be tested, and allows the entire row to change color
For some reason when I change the words Open / Cancelled etc the cell colours are not changing!
Where I am right now, I only have 2007, so any testing I do here will not really help you. I will be home in a few hours and have 2000 there (as well as 2007), so I will take a look later
Re: Trying to change Row colours on field triggers - either date and or words
Thank you so much I really appreciate your help
OK, found the problem. Change all references in the CF rules to 5....
1. highlight the entire range you want to change color.
2. for the 1st rule, use this =OR($M5="Cancelled",$M5="Closed")
3. for the 2nd rule use this =$M5="Open"
4. for the 3rd rule use this =($K5-TODAY())<5
Re: Trying to change Row colours on field triggers - either date and or words
Cant believe I didnt pick that up!! All Fixed!! Thank you so much for your time I really appreciate yor continued help! Have a great day!
Happy to help, and glad we got it solved (we all overlook little things like that sometimes, no biggie)
Sorry, I have found another problem with the formula! The date trigger to turn the row red if the date is due to expire in 5 days time isnt changing the row to red "open" - it is remaining green "open" I initially had the fomula reading to the wrong cell but fixed that but it hasnt fixed the problem - so Cell "L5" is the date trigger to alarm me when something is due to expire in 5 days Cell "M5" remains as "Open". The cell colours are changing to grey if I change the cell to closed or cancelled it is just the "Red trigger" that isnt woking. I hope this makes sense!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks