Conditional Formatting | Color Cells Within Certain Dates

[urban_player]

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

 

[Yongle]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]a[/td][td]Ticket Ref [/td][td]c[/td][td]d[/td][td]date logged [/td][td]e[/td][td]

FALSE​

[/td][td] =AND(E1>=TODAY(),E1<=TODAY()+7,B1<>"")[/td][td]

FALSE​

[/td][td] =AND(E1>TODAY()+7,E1<=TODAY()+30,B1<>"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td=bgcolor:#FF0000]ref001[/td][td][/td][td][/td][td]

13/04/2019​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td=bgcolor:#FF0000]ref002[/td][td][/td][td][/td][td]

15/04/2019​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td=bgcolor:#C6E0B4]ref003[/td][td][/td][td][/td][td]

17/04/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td=bgcolor:#C6E0B4]ref004[/td][td][/td][td][/td][td]

24/04/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td=bgcolor:#C6E0B4]ref005[/td][td][/td][td][/td][td]

27/04/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td=bgcolor:#C6E0B4]ref006[/td][td][/td][td][/td][td]

30/04/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td=bgcolor:#C6E0B4]ref007[/td][td][/td][td][/td][td]

03/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td=bgcolor:#C6E0B4]ref008[/td][td][/td][td][/td][td]

06/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td=bgcolor:#C6E0B4]ref009[/td][td][/td][td][/td][td]

09/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td]ref010[/td][td][/td][td][/td][td]

12/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[urban_player]

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]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]a[/td][td]Ticket Ref [/td][td]c[/td][td]d[/td][td]date logged [/td][td]e[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td=bgcolor:#FF0000]ref001[/td][td][/td][td][/td][td]

11/04/2019​

[/td][td][/td][td]

TRUE​

[/td][td] =AND(E7>=TODAY(),E7<=TODAY()+7,B7<>"")[/td][td]

FALSE​

[/td][td] =AND(E7>TODAY()+7,E7<=TODAY()+30,B7<>"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td=bgcolor:#FF0000]ref002[/td][td][/td][td][/td][td]

12/04/2019​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td=bgcolor:#FF0000]ref003[/td][td][/td][td][/td][td]

13/04/2019​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td=bgcolor:#C6E0B4]ref004[/td][td][/td][td][/td][td]

29/04/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td=bgcolor:#C6E0B4]ref005[/td][td][/td][td][/td][td]

01/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td=bgcolor:#C6E0B4]ref006[/td][td][/td][td][/td][td]

03/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td=bgcolor:#C6E0B4]ref007[/td][td][/td][td][/td][td]

05/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td=bgcolor:#C6E0B4]ref008[/td][td][/td][td][/td][td]

07/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td=bgcolor:#C6E0B4]ref009[/td][td][/td][td][/td][td]

09/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td]ref010[/td][td][/td][td][/td][td]

11/05/2019​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
NOTE
I assume you meant B7:B300 this is what you said originally
highlight cell in column B as colour