Conditional Formatting | Color Cells Within Certain Dates

urban_player

New Member
Joined
Feb 22, 2019
Messages
14
Hi All,

I am trying to accomplish the following:

1) If column B contains text and the date entered in column E is within 1 week from today's date, highlight cell in column B as colour
2) If column B contains text and the date entered in column E is within 1 month from today's date, highlight cell in column b as colour

Column B contains a ticket reference
Column E contains dates for when the ticket is logged

Any assistance is greatly appreciated.

Many thanks!

urban
 

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.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Select column B \ click on Conditional formatting \ add new rule with formula per H1
Select column B \ click on Conditional formatting \ add new rule with formula per J1
I usually test the formula in the worksheet first (as below in columns G & I) and then delete them

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
1
aTicket Ref cddate logged e
FALSE​
=AND(E1>=TODAY(),E1<=TODAY()+7,B1<>"")
FALSE​
=AND(E1>TODAY()+7,E1<=TODAY()+30,B1<>"")
2
ref001
13/04/2019​
TRUE​
FALSE​
3
ref002
15/04/2019​
TRUE​
FALSE​
4
ref003
17/04/2019​
FALSE​
TRUE​
5
ref004
24/04/2019​
FALSE​
TRUE​
6
ref005
27/04/2019​
FALSE​
TRUE​
7
ref006
30/04/2019​
FALSE​
TRUE​
8
ref007
03/05/2019​
FALSE​
TRUE​
9
ref008
06/05/2019​
FALSE​
TRUE​
10
ref009
09/05/2019​
FALSE​
TRUE​
11
ref010
12/05/2019​
FALSE​
FALSE​
Sheet: Sheet1
 

urban_player

New Member
Joined
Feb 22, 2019
Messages
14
Hola Yongle,

Thank you for your post.

I created two conditional formatting rules.
One rule contains =AND(E7>=TODAY(),E7<=TODAY()+7,B7<>"")
The other: =AND(E7>TODAY()+7,E7<=TODAY()+30,B7<>"")

When i apply these to cells A7:A3000
Nothing works :(
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I do not know what you are doing wrong but this works

- select B7 to B3000 NOTE cell B7 must be the first cell in selected range
- add new CF rule
- select use formula to determine
- formula in H7 pasted into Format values where this is true
- added red colour fill format
- added another rule for formula in J7
- added green colour fill format

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
4
5
6
aTicket Ref cddate logged e
7
ref001
11/04/2019​
TRUE​
=AND(E7>=TODAY(),E7<=TODAY()+7,B7<>"")
FALSE​
=AND(E7>TODAY()+7,E7<=TODAY()+30,B7<>"")
8
ref002
12/04/2019​
TRUE​
FALSE​
9
ref003
13/04/2019​
TRUE​
FALSE​
10
ref004
29/04/2019​
FALSE​
TRUE​
11
ref005
01/05/2019​
FALSE​
TRUE​
12
ref006
03/05/2019​
FALSE​
TRUE​
13
ref007
05/05/2019​
FALSE​
TRUE​
14
ref008
07/05/2019​
FALSE​
TRUE​
15
ref009
09/05/2019​
FALSE​
TRUE​
16
ref010
11/05/2019​
FALSE​
FALSE​
Sheet: Sheet1
NOTE
I assume you meant B7:B300 this is what you said originally
highlight cell in column B as colour
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,641
Members
416,124
Latest member
DeMoNloK

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