Conditional Formatting for a cell when empty and when it contains a date

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
123
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am looking for a CF that will give me the following

1. When the cell A1 is empty I want the cell to have no format relative to the contents of the cell B1 that contains the CF
2. When cell A1 contains a date that is great than the date in cell B1 then A1 should be Red
3. When the date in cell A1 is less than or equal to the date in B1 then A1 should be Green

I did have a very simple CF formula for 2 & 3

For 2: Cell value <= B1 Format Green
For 3: Cell value > B1 Format Red

The problem I have is when A1 is empty with no date, the cell is green due to the CF. If A1 is empty I want the cell I want the cell to have no format.

Hopefully this makes sense. ( Fingers crossed)

Best Regards

Marcie Be
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,865
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=AND(A1<>"",A1<=B1)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,394
Office Version
  1. 365
Platform
  1. MacOS
For 2: Cell value <= B1 Format Green
use a formula
Select A1 and use CF
Formula
=AND ( A1 <> "" , A1 < = B1 )
Format green
 

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
123
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

For 2: Cell value <= B1 Format Green
use a formula
Select A1 and use CF
Formula
=AND ( A1 <> "" , A1 < = B1 )
Format green
Thank you @etaf
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,865
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
123
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

@Fluff This formula gave me the out come I as looking for other than another CF affecting Cell B1 has now been affected.
I also have a CF in Cell B1 Formula:=$C5="Data Transfer" Format Grey. This CF no longer works because of the new CF =AND ( A1 <> "" , A1 < = B1 )
Can you help resolve please?
Best Regards
Marcie Be
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,865
Office Version
  1. 365
Platform
  1. Windows
What are the ranges those rules are applied to?
 

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
123
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What are the ranges those rules are applied to?
Range
$A1 to $A12
$B1 to $B12

Actually the formula :=$C5="Data Transfer" could be $C1

I have simplified the cell locations to A, B & C. In reality that are part of a much larger worksheet where the locations start at $AG6 & $AK6
I hope this helps
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

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