Excel 2013 Conditional Formatting - Domain Renewal Spreadsheet

I have been scratching my head and searching the internet for the past 3 hours on how exactly to achieve what I want! I'm almost tempted to do this by hand but I want to automate it if possible!

I've created a spreadsheet for all the domains under our control and I want the date to go orange if it's due for renewal in the next 3 months, green if it's anything further in the future.

Here's a snapshot of what I've got so far. Please disrgard any colour/formatting visible. I've stripped the domains for confidentiality reasons but the first two columns are currently fully populated. I'd like the entire row to be formatted with the colouring as it shows currently.

Excel Spreadsheet
LVL 1
UncleVirusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Try this:

Select the whole range of data, e.g. B4:H100 or similar then apply conditional formatting conditions as they need to be applied to the top left cell (B4) in that range, i.e. use this condition first

=$E4>=TODAY()

with orange format

....and then add

=$E4>EDATE(TODAY(),3)

with greeen format

Excel applies these in reverse order of the order they are added so the second formula is applied first (!) and any dates more than 3 months in the future are formatted green.....then out of the remaining dates you get orange for those that are >=TODAY()

Using $ with $E4 is required to format the whole row

See attached example where I have some random dates in column E - press F9 to re-generate dates

regards, barry
dates-example.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UncleVirusAuthor Commented:
Barry, you Sir, are a legend!

The correct method and feeding the correct formulae into the Conditional Formatting box was where I was tripping up. Seeing your visual example enabled me to adapt my spreadsheet accordingly in no time at all.

Thank you very much :-)
0
barry houdiniCommented:
>Barry, you Sir, are a legend!

Thanks for the compliment UncleVirus (is there an AuntVirus?), but at EE "Legend" is a term specifically applied to somebody with 100 million points (there are currently no legends as nobody has more than 39 million points).

....still, only another 95,214,925 to go!

regards, barry
0
UncleVirusAuthor Commented:
One point at a time eh? I have another conundrum on the same spreadsheet involving adding a button/macro to increment the date by 1 year if you fancy another challenge ;-) I'll raise a new question for that though! :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.