Help - 3 conditional formats clashing

Kmom

New Member
Joined
Sep 6, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi

Please help this novice!

I have a column of dates which I'd like to apply 3 possible colours to.
  1. If blank = yellow (I used 'Format only cells that contain blanks')
  2. If date is in the past = red (I used 'Format only cells that with cell value less than =TODAY()' )
  3. If date is with 274 days of today (future)= amber (this is the one I probably need help with - have tried various formulas)

Here's my challenges:
  • I can get all three conditions to work on their own
  • I can get any combo of two conditions to work
  • As soon as I add a third it stops working
  • I've tried several formulae all of which work independently but not in the group of 3
  • No matter what order I place them in I cannot make all three work
  • No matter what combo of order and 'stop if true' I use I cant make it work
  • I've driven myself insane and now cant work out how to apply any rules down a list - Ive tried ever option in term s of $ $$ or no $ and cant get me head around it (have tried format painter into a blank column as well)
  • Literally losing my mind!

PLEASE HELP

Thanks
K Mom ;)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,108
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
G
2
301/09/2021
423/08/2021
515/09/2021
615/09/2022
706/06/2022
807/06/2022
906/09/2021
1003/09/2020
11
12
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G12Expression=G2=""textNO
G2:G12Expression=G2<TODAY()textNO
G2:G12Expression=G2<TODAY()+274textNO
 
Solution

Kmom

New Member
Joined
Sep 6, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
How about
+Fluff 1.xlsm
G
2
301/09/2021
423/08/2021
515/09/2021
615/09/2022
706/06/2022
807/06/2022
906/09/2021
1003/09/2020
11
12
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G12Expression=G2=""textNO
G2:G12Expression=G2<TODAY()textNO
G2:G12Expression=G2<TODAY()+274textNO
This works PERFECTLY
THANK YOU!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,108
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,286
Members
425,460
Latest member
Astros1243

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