Coloring Cells when Date is inputed

[Sam Lam]

Hi all,

I would like to colour the cells in between the start and end date automatically once i got the dates from the user. Any idea on how to do it?

 

[Peter_SSs]

Welcome to the MrExcel board!

You will generally get better/faster help if you can provide board members with sample data that they can easily copy from.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if using Conditional Formatting like this does what you want.

23 08 18.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1					20-Jul		21-Jul		22-Jul		23-Jul		24-Jul		25-Jul		26-Jul		27-Jul		28-Jul		29-Jul		30-Jul
2					D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N
3	23-Jul	26-Jul
4	2-May	5-Jun
5
6	23-Jul	23-Jul
Colour cells

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:Z6	Expression	=LET(d,LOOKUP(9^9,$E$1:E$1),AND(d>=$A3,d<=$B3))	text	NO

 

[Sam Lam]

Hi Peter, I have pasted the formula into the conditional formatting bar but nothing seems to work. (I dont have a strong good foundation in excel and this is my intern job :/) May I know if I have messed up something?

 

[Peter_SSs]

We cannot see your rows & columns. Can you post the sample data with XL2BB as suggested earlier?

 

[Sam Lam]

Hi Peter, the laptop is under company's supervision and it says cant be opened in protected view. However I have this larger screenshot of the file. Hope it helps.

 

[Peter_SSs]

says cant be opened in protected view.

For next time, review the "Known XL2BB issues" near the top of the instructions. That will likely resolve that issue.

For now,

• remove the CF from the worksheet (Conditional Formatting -> Clear Rules -> Clear Rules from Entire Sheet)
• Select from F4 to whatever your last column is & down to row 17. Just check that F4 is the active cell
• Apply the CF as shown below

23 08 18.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2						20-Jul		21-Jul		22-Jul		23-Jul		24-Jul		25-Jul		26-Jul		27-Jul		28-Jul		29-Jul		30-Jul
3						D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N	D	N
4		23-Jul	26-Jul
5		2-May	5-Jun
6
7		23-Jul	23-Jul
8
Colour cells

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:BA17	Expression	=LET(d,LOOKUP(9^9,$F$2:F$2),AND(d>=$B4,d<=$C4))	text	NO