Hi,
I have customer based data with having date of birth in dd-mmm-yyyy format ( 20-oct-1980 ), I want formula / macro to highlight entire row if date of birth match with todays date with red colour. also date of birth if comming next 5 days in green colour.
Pls help.
use conditional format
Two Formulas to add
=AND((TODAY()-5)<=$A7, (TODAY()-1)>=$A7)
format green
and
=TODAY()=$A7
format red
see attached sheet
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
Range you want to apply the Conditional format to
A1:M200 for example
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:
=AND((TODAY()-5)<=$A7, (TODAY()-1)>=$A7)
Format… [Number, Font, Border, Fill]
OK >> OK
Repeat for RED format
=TODAY()=$A7
Last edited by etaf; 10-20-2013 at 05:22 AM.
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.
Many thanks for reply.
its work!!! but date of birth in column is not 2013 it should match only dd-mmm with today. pls help
if date of birth in coloumn 10-oct-1980 then row should be in red on 10-oct every year.
Try using DAY & MONTH functions.
Formula:
Please Login or Register to view this content.
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.
Hello Dang Try this
=AND(MONTH(A1)=MONTH(TODAY()),DAY(A1)-DAY(TODAY())>0,DAY(A1)-DAY(TODAY())<6) in CF
Last edited by hemesh; 10-21-2013 at 01:58 AM. Reason: @Fotis Forgot to Refresh
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks