How do I make excel change the colour of a cell depending on a different cells date?

%3CLINGO-SUB%20id%3D%22lingo-sub-100436%22%20slang%3D%22en-US%22%3EHow%20do%20I%20make%20excel%20change%20the%20colour%20of%20a%20cell%20depending%20on%20a%20different%20cells%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22001.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F19147iBFBC85954F863637%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22001.png%22%20alt%3D%22001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20make%20excel%20change%20the%20colour%20of%20a%20cell%20depending%20on%20a%20different%20cells%20date%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETurn%20A2%20red%20if%20-%20E2%20cell%20is%20smaller%20than%20todays%20date%3C%2FP%3E%3CP%3ETurn%20A2%20yellow%20with%20red%20outline%20if%20-%20E2%20cell%20is%20equal%20to%20todays%20date%3C%2FP%3E%3CP%3ETurn%20A2%20clear%20if%20-%20E2%20cell%20is%20bigger%20than%20the%20current%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-100436%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETutorial%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100702%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20do%20I%20make%20excel%20change%20the%20colour%20of%20a%20cell%20depending%20on%20a%20different%20cells%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100702%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecolumn%20E%20does%20not%20have%20dates.%20Column%20F%20does%2C%20and%20so%20does%20column%20H.%20In%20the%20following%20I%20assume%20you%20want%20to%20use%20the%20date%20in%20column%20F.%20Change%20the%20formula%20to%20use%20H%20if%20you%20want%20to.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20cell%20A2%3C%2FLI%3E%3CLI%3Eclick%20%3CSTRONG%3EConditional%20Formatting%3C%2FSTRONG%3E%20on%20the%20Home%20ribbon%3C%2FLI%3E%3CLI%3Eclick%26nbsp%3B%3CSTRONG%3ENew%20Rule%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3Eclick%26nbsp%3B%3CSTRONG%3EUse%20a%20formula%20to%20determine%20which%20cells%20to%20format%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3Eclick%20into%20the%20formula%20box%20and%20enter%20the%20formula%3CBR%20%2F%3E%3D%24F2%3CTODAY%3E%3C%2FTODAY%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222017-08-25_15-57-20.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F19190i438E1E60B59B973A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222017-08-25_15-57-20.png%22%20alt%3D%222017-08-25_15-57-20.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3Eclick%20the%20Format%20button%20and%20select%20a%20red%20color%3C%2FLI%3E%3CLI%3Eclose%20all%20dialogs%3C%2FLI%3E%3C%2FOL%3E%3CP%3ERepeat%20the%20steps%20and%20use%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%24F2%3DToday()%3C%2FP%3E%3CP%3Eand%20select%20a%20yellow%20fill%20and%20red%20border.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20select%20cell%20A2%20again%2C%20click%20the%20Format%20Painter%20in%20the%20home%20ribbon%2C%20then%20select%20all%20other%20cells%20below%20cell%20A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100614%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20do%20I%20make%20excel%20change%20the%20colour%20of%20a%20cell%20depending%20on%20a%20different%20cells%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100614%22%20slang%3D%22en-US%22%3EI%20don't%20understand%2C%20Sorry%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100531%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20excel%20change%20the%20colour%20of%20a%20cell%20depending%20on%20a%20different%20cells%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100531%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Daniel%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20screenshot%20there%20is%20no%20any%20date%20in%20E2.%20But%20in%20general%3C%2FP%3E%3CP%3EStay%20on%20A2%2C%20Home-%26gt%3BConditional%20formatting-%26gt%3BManage%20rules-%26gt%3BNew%20ruke%3B%20use%20for%20the%20ruke%3C%2FP%3E%3CPRE%3E%3D%24E2%26lt%3B%3DTODAY()%3C%2FPRE%3E%3CP%3EFormat%2C%20apply%20any%20formatting%20you%20want.%20After%20that%20apply%20the%20rule%20to%20entire%20range%20(e.g.%20%24A%242%3A%24A%2410)%2C%20or%20keep%20only%20A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERepeat%20for%20each%20color%20%2Fcondition%20you'd%20like%20to%20have%3C%2FP%3E%3CP%3E%26nbsp%3B%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
New Contributor

001.png

Hi,

 

How do I make excel change the colour of a cell depending on a different cells date?

 

Turn A2 red if - E2 cell is smaller than todays date

Turn A2 yellow with red outline if - E2 cell is equal to todays date

Turn A2 clear if - E2 cell is bigger than the current date

 

4 Replies

Hi Daniel,

 

In your screenshot there is no any date in E2. But in general

Stay on A2, Home->Conditional formatting->Manage rules->New ruke; use for the ruke

=$E2<=TODAY()

Format, apply any formatting you want. After that apply the rule to entire range (e.g. $A$2:$A$10), or keep only A2.

 

Repeat for each color /condition you'd like to have

 

 

 

 

Hello,

 

column E does not have dates. Column F does, and so does column H. In the following I assume you want to use the date in column F. Change the formula to use H if you want to. 

 

  1. Select cell A2
  2. click Conditional Formatting on the Home ribbon
  3. click New Rule
  4. click Use a formula to determine which cells to format
  5. click into the formula box and enter the formula
    =$F2<Today()
    2017-08-25_15-57-20.png
  6. click the Format button and select a red color
  7. close all dialogs

Repeat the steps and use the formula

 

=$F2=Today()

and select a yellow fill and red border.

 

Next, select cell A2 again, click the Format Painter in the home ribbon, then select all other cells below cell A2.

 

Does that help?

@Ingeborg Hawighorst 

 

Do you happen to know how to change the cell colour based on an other cell colour? 

 

IE we have a shipping sheet with user name, address, contact number, items in box, box size, tracking number etc and the tracking number cell column is highlighted a colour based on its delivery (we change it to green once delivered) but we'd like the first cell in the row (or whole row) to also change green showing that whole line is complete. 

I cannot find anything to support this