Highlighting Cells in Excel based on the Value Associated with the Cell

Question

I have two sheets:

1. Users in their respective groups and
2. A compilation of those same users with an expiration date.

I'd like to highlight any cell in the the first sheet if their respective date is less than the current day.

EDIT

Using tylyn's formula, I'm trying to add a secondary rule that compares today's date and the date listed in the table and if that number is less than 30 days, format a different color.

Here is what I tried: =VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE)>TODAY()*TODAY()-VLOOKUP(A1,'Sheet Name 2'!$A$1:$B$1000,2,FALSE)<30

Also tried: =DATEIF(TODAY(),VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE),"D")<30

FINAL EDIT

If I had used DATEDIF instead of DATEIF, it would have worked

Answer 1

To format the cells if their date is before the current day (Sheet 1 = groups, Sheet 2 = dates and names):

1. Go to sheet 1, select all the data (A1:D7 in your example)
2. Click Conditional Formatting > New rule > Use a formula to determine which cells to format
3. Click the Format button and change the format to whatever you want (bearing in mind that this one is for 'before the current day'. Paste this:

=VLOOKUP(A1,Sheet2!$A$1:$B$15,2,FALSE)<TODAY()

Change the Sheet2!$A$1:$B$15 to whatever is right for your spreadsheet (this is correct for your example). Make sure you only put the $ in where they are above (they are for absolute references)

---

To format the cells if their date is within the next 30 days:

1. Go to sheet 1, select all the data (A1:D7 in your example)
2. Click Conditional Formatting > New Rule > Use a formula to determine which cells to format
3. Click the Format button and change the format to whatever you want (bearing in mind that this is the one for dates 'upcoming in the next 30 days'. Enter this formula:

=DATEDIF(TODAY(), VLOOKUP(A1,Sheet2!$A$1:$B$15,2,FALSE), "d") < 30

This looks at the date dif ference between today and the ones on Sheet 2. If they are < 30 then it is true and formats the cells. The "d" is called the interval and determines whether to look for the difference between days/months/years etc. Make sure you only put the $ in where they are above (they are for absolute references)

---

The important thing now is to go to the Conditional Formatting button > Manage Rules > Show Formatting Rules for: This Worksheet. Click the second formula (it should be the one with DATEDIF and click the down arrow at the top right. This sets priority to your first formula.

---

Screenshots:

This look a bit different because you had an incorrect date in your example (yadyr):

Answer 2

Assuming the groups are showing on Sheet1, the list of names and dates is on sheet 2:

• select the table with the groups, including blank cells, starting in A1
• click Conditional Formatting > New Rule > Use a formula to determine
• enter the formula posted below, click the Format button and select the fill

Formula:

=VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)<TODAY()

Adjust the range of the lookup table to suit your data in sheet 2.

Note that there are no $ signs in the first A1 reference. This is important. If the top left cell of the selected table is not A1, you need to use the cell address of the top left table that you have selected.

This way the conditional format will look at the current cell, even if the format is extended / copied to other rows or columns.