SOLVED

# Conditional Formatting for different cell values each row

Occasional Contributor

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

9 Replies
best response confirmed by sanjurk (Occasional Contributor)
Solution

# Re: Conditional Formatting for different cell values each row

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.

# Re: Conditional Formatting for different cell values each row

Thanks @Hans Vogelaar

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.

# Re: Conditional Formatting for different cell values each row

Spoiler
@Hans Vogelaar

Could you also let me know, if I want to add text to these Coloured Cells, what should I enter. Like for the coloured cells, I want to add "AL" - which means Annual Leave

# Re: Conditional Formatting for different cell values each row

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.

# Re: Conditional Formatting for different cell values each row

Will those cells contain other data?

If so, will at always be a number? Or could it be text?

# Re: Conditional Formatting for different cell values each row

@Hans Vogelaar- The cell is as shown in the picture.

The cells are blank, when I enter the dates To and From - it will display it with the colour and mention a text as "AL"

# Re: Conditional Formatting for different cell values each row

Enter the following formula in E3:

=IF(AND(E\$2>=\$C3,E\$2<=\$D3),"AL","")

Fill or copy down, then to the right.

# Re: Conditional Formatting for different cell values each row

I tried it out, but it doesnt work.

I inserted the earlier one and the new one as two different formulas and still doesnt work.

# Re: Conditional Formatting for different cell values each row

Could you attach a sample workbook without sensitive information? Thanks in advance.