If 5 days since initial date, turn cell red

%3CLINGO-SUB%20id%3D%22lingo-sub-1839795%22%20slang%3D%22en-US%22%3EIf%205%20days%20since%20initial%20date%2C%20turn%20cell%20red%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20a%20beginner%20when%20it%20comes%20to%20excel%20and%20am%20needing%20some%20resources%2Fguidance.%3C%2FP%3E%3CP%3EI%20need%20to%20find%20a%20way%20(I'm%20not%20sure%20whether%20to%20use%20%22If%22%20or%20%22conditional%22)%20to%20make%20the%20cells%20in%20column%20J%20to%20turn%20red%2C%20if%20the%20matching%20cells%20in%20column%20G%20are%20more%20than%205%20days%20apart.%20Ideally%20this%20would%20be%20one%20formula%20that%20can%20apply%20to%20all%20the%20cells%20in%20those%20column%20rather%20than%20individually%20applying%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advise%20would%20be%20appreciated!%20Thank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Natalie907_0-1604088834116.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230535i8F2B72D66FCD5DEE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Natalie907_0-1604088834116.png%22%20alt%3D%22Natalie907_0-1604088834116.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1839795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1839841%22%20slang%3D%22en-US%22%3ERe%3A%20If%205%20days%20since%20initial%20date%2C%20turn%20cell%20red%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852339%22%20target%3D%22_blank%22%3E%40Natalie907%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20J2%3AJ100%20(or%20however%20far%20down%20the%20data%20go).%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20J2%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'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20dropdown%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'greater%20than'%20from%20the%20second%20dropdown.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DG2%2B5%20in%20the%20box%20next%20to%20it.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20red.%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-1839843%22%20slang%3D%22en-US%22%3ERe%3A%20If%205%20days%20since%20initial%20date%2C%20turn%20cell%20red%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852339%22%20target%3D%22_blank%22%3E%40Natalie907%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20conditional%20formatting%20rule%20to%20the%20column%20%22Submit%22%20of%20the%20table%20with%20rule%20formula%20as%20%3D%24J2%26gt%3B%24G2%2B5.%3C%2FP%3E%0A%3CP%3EIf%20you%20provide%20sample%20file%20instead%20of%20screenshot%20it'll%20be%20easier%20to%20illustrate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1839995%22%20slang%3D%22en-US%22%3ERe%3A%20If%205%20days%20since%20initial%20date%2C%20turn%20cell%20red%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20here%20is%20a%20sample%20file.%20I%20will%20try%20to%20apply%20your%20suggestion!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there,

 

I'm a beginner when it comes to excel and am needing some resources/guidance.

I need to find a way (I'm not sure whether to use "If" or "conditional") to make the cells in column J to turn red, if the matching cells in column G are more than 5 days apart. Ideally this would be one formula that can apply to all the cells in those column rather than individually applying the formula. 

Any advise would be appreciated! Thank you! 

Natalie907_0-1604088834116.png

 

8 Replies
Highlighted

@Natalie907 

Select J2:J100 (or however far down the data go).

I will assume that J2 is the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first dropdown set to 'Cell Value'.

Select 'greater than' from the second dropdown.

Enter the formula =G2+5 in the box next to it.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

Highlighted

@Natalie907 

You may apply conditional formatting rule to the column "Submit" of the table with rule formula as =$J2>$G2+5.

If you provide sample file instead of screenshot it'll be easier to illustrate.

Highlighted

@Sergei Baklan  here is a sample file. I will try to apply your suggestion!

Highlighted

@Natalie907 

See the attached version.

Click in J2 and select Conditional Formatting > Manage Rules... to view the conditional formatting rule.

Highlighted
OMG I am taking stats on-line. Never used excel before last week. I need to change datasheet into a chart/graph. HELP!!
Highlighted

@123Stats 

Please post that as a new question instead of as a reply in an existing one.

Highlighted

@Hans Vogelaar This is the right idea but I'm needing each cell to be dependent on the cell date in the same row.

Example based off the demo: I need column J dates, to coincide with the cell in the same row for column H. 

 "LE Recv" date + 7 (or 5 business days) and anything beyond to turn the cell red that's in in the same row but in the "Submit" column J. 

Do I need to manually go in to make each cell coincide with the cell in the same row? Or can I create a formula for the whole table?

Highlighted

@Natalie907 

If I understand you correctly:

If you want to format cells in column J, select J2:J20, or if you want to format the entire row in the table, select G2:K20.

I will assume that the acfive cell in the selection is in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain.

Leave the first dropdown set to 'Cell Value'.

Select 'greater than or equal to' from the second dropdown.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

 

See the attached, modified version.

Enter the formula =WORKDAY($H2,5) in the box next to it.