• 510K Members
• 4,234 Online
• 607K Conversations

New Contributor

# Conditional Formatting

Hi all

I’m trying to get cell D in the attached photo to change either red if cell C is before today’s date, Green if cell C is after today’s date and Clear if no data is in Cell C

How do I also get this to happen to approx 150 cells? With out it populating all the way down cell D as in the other Photo

3 Replies

# Re: Conditional Formatting

Whoa, you may want to learn how to use the Snipping tool that is built into your computer, so you can take screenshots with software instead of using a camera to photograph what's on your monitor.

You can also attach a sample file to a post here. Use the "Choose Files" button below the reply box to attach a file with your post.

It seems that you have two questions. One is about conditional formatting, and the other one is about  how to show the date only in rows that have a real date in column C.

How to show next refresher due only for rows with a date in column C: Start in D4 with this formula and copy down.

=IF(C4>0,DATE(YEAR(C4)+1,MONTH(C4),DAY(C4)),"")

How to use conditional formatting to highlight cells:

Select cell D4 down to the bottom of your list. Then click Conditional formatting > New Rule > Use a formula to determine ... > and enter this formula into the formula box:

=AND(ISNUMBER(\$C4),\$C4>TODAY())

use green for that.

Then add another rule with this formula:

=AND(ISNUMBER(\$C4),\$C4<TODAY())

Use red for that.

I'm not sure if that is really what you want to show, but this does what you described. If the date in C is less than today, D will be red. If the date in C is greater than today, D will be green. If there is no date in C, D will not change colour.

It does not really make sense that the last refresher training in C would be a date that is greater than today, but that is what you asked for.

The screenshot shows the formula for column D in the formula bar and the rule for the green conditional format following your requirements (which don't make sense), because the last refresher training cannot be in the future. Or can it?

I'm attaching the file so you can have a play with it .

# Re: Conditional Formatting

Thank you so much, its done the trick.

Sorry about the picture I do know about snipping tool I just couldn't use it to be able to do this, as I couldn't get to this site on my work computer

The purpose of it is to show when we will need to contact people to do training

Last refresher training wont be the future, that will be the past and next refresher will be a future date dependent on when the last training was conducted

# Re: Conditional Formatting

@Ingeborg Hawighorst Sorry looks like something funny is going on.

anything before the current day needs to be Red and anything that is in the future needs to be Green in the Next refresher training cell

Also if you were to input the current day into the last refresher training it fills in the next refresher with a clear box, how could I go about having that as green aswell?

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies