Conditional Formatting

downunder400

New Member
Joined
Aug 24, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm trying to activate conditional formatting to an excel sheet so when a date hits 60 days before expiry highlight it yellow, when it hits 30 days highlight it orange and when it expires highlight it red.

Is this possible and if so how?!

Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,396
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Can you post some sample data using the XL2BB add-in.
 

downunder400

New Member
Joined
Aug 24, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
dateSiteNameCompany NameInduction NoInduction Expiry
12/01/2018Altens
1​
12/01/2020​
06/02/2018Altens
2​
06/02/2020​
06/02/2018Altens
3​
06/02/2020​
06/02/2018Altens
4​
06/02/2020​
06/03/2018Altens
5​
05/03/2020​
 

downunder400

New Member
Joined
Aug 24, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
So in Column F, there are numerous dates from over 6 months ago to 6 months away yet. How do I highlight them so when a date hits 60 days away from the day the sheet was opened it turns them yellow, then 30 days away turns them orange, then when the date is the day the sheet is opened or past they are red
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,396
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Unless I'm missing something all those dates would be highlighted in red.
 

downunder400

New Member
Joined
Aug 24, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
For the selection I have sent yes they would all be red but the full sheet has dates in the future too as late as November this year
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,396
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In future it would help if you supplied a realistic sample showing all the various possibilities. ;)
How about
+Fluff New.xlsm
ABCDEF
1dateSiteNameCompany NameInduction NoInduction Expiry
212/01/2018Altens123/08/2020
306/02/2018Altens225/08/2020
406/02/2018Altens323/09/2020
506/02/2018Altens424/09/2020
606/03/2018Altens525/12/2020
7
8
9
Data
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,396
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to include the CF rules
+Fluff New.xlsm
ABCDEF
1dateSiteNameCompany NameInduction NoInduction Expiry
212/01/2018Altens123/08/2020
306/02/2018Altens225/08/2020
406/02/2018Altens323/09/2020
506/02/2018Altens424/09/2020
606/03/2018Altens525/12/2020
7
8
9
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F9Expression=AND(F2<>"",F2<=TODAY())textNO
F2:F9Expression=AND(F2<>"",F2<=TODAY()+30)textNO
F2:F9Expression=AND(F2<>"",F2<=TODAY()+60)textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,127,301
Messages
5,623,855
Members
415,995
Latest member
SergioCM92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top