SOLVED

Conditional Formatting for different cell values each row

%3CLINGO-SUB%20id%3D%22lingo-sub-1992802%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20for%20different%20cell%20values%20each%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1992802%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20prepare%20a%20table%20where%20I%20will%20be%20entering%20the%20dates%20of%20employee%20leave.%3C%2FP%3E%3CP%3EI%20need%20to%20prepare%20a%20sheet%20in%20such%20a%20way%20that%20whenever%20I%20enter%20the%20From%20and%20To%20date%2C%20it%20should%20colour%20the%20cells%20accordingly%20as%20per%20the%20date%20mentioned%20above.%20I%20have%20attached%20a%20screen%20shot%20of%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1992802%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993417%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20different%20cell%20values%20each%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795969%22%20target%3D%22_blank%22%3E%40sanjurk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20range%20that%20you%20want%20to%20format%20(E3%3ANE8).%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20E3%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(E%242%26gt%3B%3D%24C3%2CE%242%26lt%3B%3D%24D3)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1996602%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20different%20cell%20values%20each%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1996602%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%20like%20a%20charm.%20I%20am%20still%20trying%20to%20figure%20out%20what%20does%20the%20formula%20convey.%20Is%20there%20any%20way%20you%20could%20explain%20%2C%20how%20does%20that%20formula%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997006%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20different%20cell%20values%20each%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997006%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20also%20let%20me%20know%2C%20if%20I%20want%20to%20add%20text%20to%20these%20Coloured%20Cells%2C%20what%20should%20I%20enter.%20Like%20for%20the%20coloured%20cells%2C%20I%20want%20to%20add%20%22AL%22%20-%20which%20means%20Annual%20Leave%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3BCould%20you%20also%20let%20me%20know%2C%20if%20I%20want%20to%20add%20text%20to%20these%20Coloured%20Cells%2C%20what%20should%20I%20enter.%20Like%20for%20the%20coloured%20cells%2C%20I%20want%20to%20add%20%22AL%22%20-%20which%20means%20Annual%20Leave%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997271%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20different%20cell%20values%20each%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795969%22%20target%3D%22_blank%22%3E%40sanjurk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20%3DAND(E%242%26gt%3B%3D%24C3%2CE%242%26lt%3B%3D%24D3)%20is%20for%20cell%20E3%20(the%20active%20cell%20in%20the%20selection).%20Excel%20will%20automatically%20adjust%20it%20for%20the%20other%20cells%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EE%242%20is%20the%20cell%20in%20the%20same%20column%20in%20row%202.%20This%20is%20the%20date%20you%20want%20to%20look%20at.%3C%2FP%3E%0A%3CP%3E%24C3%20is%20the%20cell%20in%20the%20same%20row%20in%20column%20C%3A%20the%20Date%20From.%3C%2FP%3E%0A%3CP%3E%24D3%20is%20the%20cell%20in%20the%20same%20row%20in%20column%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%20the%20Date%20To.%3C%2FP%3E%0A%3CP%3EThe%20formula%20returns%20TRUE%20if%20the%20date%20in%20E%242%20is%20on%20or%20after%20the%20Date%20From%2C%20and%20also%20on%20or%20before%20the%20Date%20To%2C%20and%20FALSE%20otherwise.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@sanjurk 

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

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

@sanjurk 

 

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.

@sanjurk 

Will those cells contain other data?

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

@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"

@sanjurk 

Enter the following formula in E3:

 

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

 

Fill or copy down, then to the right.

@Hans Vogelaar 

 

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. 

@sanjurk 

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