Highlighting range of cells based on start and end dates
Hi
New to this.
I am trying to highlight a range of cells based on the start and end dates in 2 othe columns.
I.e. Column B contains Start date, Column C contains End date.
Row 2 contains Dates for 2012 by days
I would like to be able to highlight the repective cells green when a start and end date are entered.
Thank you in advance
Last edited by d0gp1l3; 07-13-2012 at 09:06 AM. Reason: Adding file
select the full range from F3 to bottom right cell of the range... then go to Home|Conditional Formatting.. choose New Rule, then select "use a formula to determine which cells to format" from top section. Then enter formula:
=AND(SUM($C3:$B3)>0,F$2>=$B3,F$2<=$C3)
click Format and choose from Fill tab.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Brilliant, been struggling with this for a while now.
Should have come here first.
You are magic, thanks
Mike
Just had another thought, would it be possible to only autosum the data in the highlighted cells?
What exactly do you mean?
I use this form to track bookings.
If I populate all cells with the cost per day the I was wondering if I could get a total for only the days booked (highlighted).
Does that make sense
You can use SUMIFS.
e.g.
in GL3:
=SUMIFS(F3:GK3,F$2:GK$2,">="&B3,F$2:GK$2,"<="&C3)
copied down
Is this what you mean?
Thanks again this is exactly what I meantOriginally Posted by NBVC
You are brilliant
Thank you again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks