# Conditional Formatting | Color Cells Within Certain Dates

#### urban_player

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Yongle

##### Well-known Member
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
Hola Yongle,

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
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
- 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:

Replies
3
Views
56
Replies
5
Views
69
Replies
7
Views
313
Replies
3
Views
51
Replies
2
Views
51

1,128,156
Messages
5,629,019
Members
416,359
Latest member
Juena

### 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.

### Which adblocker are you using?

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

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