Column A reads Jan 2014, Feb 2014... Dec 2014. I want column B to go yellow if column A has passed.
B1 and B2 are already yellow. On 1st April, B3 will go yellow to, because March 2014 in A3 will be in history.
What's my formula for the conditional formatting? See attachment.
Give me a few minutes
Conditional formatting for each cell next to the date: =A1<TODAY()
A1 being the dated cell. Bear in mind that your cells are 2015 and not 2014 so it wont turn yellow unless you change the dates
Highlight range B1:B12 and in CF rules, put this formula.
Formula:
Please Login or Register to view this content.
How can you use Conditional Formating.
--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.
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.
use
=TODAY()>=EOMONTH(A1,0)+1
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
B1:B7
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=TODAY()>=EOMONTH(A1,0)+1
Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Etaf's is first to work. Thanks all.
Thanks
Fotis1991
=MONTH(A1)<MONTH(TODAY())
works for me OK
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
Everybody's up.
Hello johnandrews,
As etaf is saying, Fotis1991's Code works just fine, as shown in the attached sample Workbook.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
The only way Fotis1991's method could be flawed, is if you continued the table to include Jan 15, Feb 15, Mar 15 etc. As the CF forumla is purely going off the MONTH within the date, so next years Jan 15 and Feb 15 would also become highlighted.
I don't doubt Fotis' works in real life; rather I probably wasn't using it properly. (I kept it on 2014 cells.)
There will always be more than one way to skin a cat and in a quick thread like this when I am over-run with an ample supply of super-helpful ubernerds, I try and play around with the different suggested formulas one at a time. Sometimes when I try and apply it to something more complicated I accidentally throw a spanner I didn't know I had in the works I wasn't aware were there.
If the best protocol is just to mark SOLVED and say 'thanks all' I'll do that in future.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks