Forum Discussion
Conditional Formatting for different cell values each row
Hi,
I need to prepare a table where I will be entering the dates of employee leave.
I need to prepare a sheet in such a way that whenever I enter the From and To date, it should colour the cells accordingly as per the date mentioned above. I have attached a screen shot of the sheet.
Select the range that you want to format (E3:NE8).
I will assume that E3 is the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(E$2>=$C3,E$2<=$D3)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
9 Replies
Select the range that you want to format (E3:NE8).
I will assume that E3 is the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(E$2>=$C3,E$2<=$D3)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Thanks HansVogelaar
That worked like a charm. I am still trying to figure out what does the formula convey. Is there any way you could explain , how does that formula work.
The formula =AND(E$2>=$C3,E$2<=$D3) is for cell E3 (the active cell in the selection). Excel will automatically adjust it for the other cells in the selection.
E$2 is the cell in the same column in row 2. This is the date you want to look at.
$C3 is the cell in the same row in column C: the Date From.
$D3 is the cell in the same row in column 😧 the Date To.
The formula returns TRUE if the date in E$2 is on or after the Date From, and also on or before the Date To, and FALSE otherwise.
