Home

Conditional Formatting - Based on Cell Value in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-736305%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20-%20Based%20on%20Cell%20Value%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736305%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20spreadsheet%20that%20has%20a%20date%20in%20column%20A.%20I%20would%20like%20Column%20B%20to%20highlight%20red%20in%20the%20case%20that%20the%20date%20in%20the%20corresponding%20cell%20in%20column%20A%20is%20due%20in%20the%20next%2030%20days.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EA3%3A%202%2F18%2F2017%20B3%3A%20should%20not%20highlight%3C%2FP%3E%3CP%3EA4%3A%207%2F12%2F2019%20B4%3A%20Should%20highlight%20red%20because%20this%20is%20within%2030%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20on%20a%20formula%20for%20this%3F%20I've%20tried%20everything%20I%20can%20think%20of.%20I%20can%20get%20it%20to%20work%20for%20a%20single%20cell%20but%20not%20for%20the%20entire%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-736305%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-736340%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Based%20on%20Cell%20Value%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736340%22%20slang%3D%22en-US%22%3EHighlight%20column%20B%20and%20try%20this%20custom%20conditional%20formatting%20formula%3A%3CBR%20%2F%3E%3DA1-TODAY()%26lt%3B%3D30%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736501%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Based%20on%20Cell%20Value%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThat%20seems%20to%20have%20turned%20the%20whole%20B%20column%20red.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737152%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Based%20on%20Cell%20Value%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370493%22%20target%3D%22_blank%22%3E%40adavis726%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3D(%24A1-TODAY()%26lt%3B%3D30)*(%24A1%26gt%3B1)%3C%2FPRE%3E%0A%3CP%3Eto%20exclude%20blank%20cells%20in%20column%20A%3C%2FP%3E%3C%2FLINGO-BODY%3E
adavis726
New Contributor

Hello everyone,

I am working on a spreadsheet that has a date in column A. I would like Column B to highlight red in the case that the date in the corresponding cell in column A is due in the next 30 days.

For example:

A3: 2/18/2017 B3: should not highlight

A4: 7/12/2019 B4: Should highlight red because this is within 30 days

 

Any thoughts on a formula for this? I've tried everything I can think of. I can get it to work for a single cell but not for the entire column.

3 Replies
Highlight column B and try this custom conditional formatting formula:
=A1-TODAY()<=30

@Twifoo That seems to have turned the whole B column red.

@adavis726 

=($A1-TODAY()<=30)*($A1>1)

to exclude blank cells in column A

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies