Hi,
I want to highlight the rows in my worksheet when the dates in column 'N' are in the past. I've seen codes to do this using conditional formatting when searching online and in here but the problem is is that it highlights blank cells as well. Is it possible to correct this?
Thanks!
So you don't want it to highlight any blank cells in that row? or you don't want it to highlight a row if column N is blank?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Let's say your data is in A2:Z450, select those cells
Go to Conditional Formatting>New Rule>Use Formula
=AND(ISNUMBER($N2), $N2< TODAY())
Does that work for you?
Give this small macro a try:
Please Login or Register to view this content.
Gary's Student
Hi guys,
Thanks so much for your responses but the solutions don't seem to be working :S. I'll attach the sheet. So if a date in the 'Loan Return' column is in the past, I want that whole row to be highlighted. I want this to happen to new data that I will be continually putting in. The problem I had before was that it seemed to highlight everything.
Thanks
The problem is dates in col. N. These are dates stored as text not numbers as excel treats dates as serial numbers. So in this case select the range A3:Q5 and make a New Rule for conditional formatting using the formula given below and set format as per your choice.
Does this help?Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Your dates are Text and not true dates (which are numbers). They need to be entered as dates. I selected A3:Q5000 and used my formula. See attached.
Hi Chemist! How do I add them as true dates for future reference? Cheers. Thanks for doing that it is exactly what I wanted.
Are you importing this data or entering it manually?
If importing, it might be better to just use sktneer's formula in the conditional formatting.
Otherwise, in a new column (which you can hide if you like)
=DATEVALUE(SUBSTITUTE(N3,".","/"))
Cell must be formatted as a date for it to look like a date.
You can then reference this new column instead of Column N or you could
Copy>Paste Special > Values into Column N
Last edited by ChemistB; 08-01-2014 at 01:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks