Home

Formatting cells based on another cell in same row

%3CLINGO-SUB%20id%3D%22lingo-sub-612667%22%20slang%3D%22en-US%22%3EFormatting%20cells%20based%20on%20another%20cell%20in%20same%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612667%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20an%20excel%20document%20and%20I%20created%20a%20conditional%20format%20in%20order%20to%20change%20the%20color%20of%20a%20cell%20based%20on%20the%20date.%20The%20formula%20is%3A%20%3DB3-TODAY()%2B365%26gt%3B%3D30%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20this%20does%20is%20tells%20the%20cell%20if%20the%20date%20falls%20anytime%20from%20the%20date%20to%20335%20days%20from%20that%20date%20to%20make%20the%20cell%20green.%20Then%20I%20have%20it%20set%20up%20within%2029%20days%20of%20365%20days%2C%2014%20days%2C%20and%205%20days%20and%20it%20changes%20to%20a%20different%20color%20based%20on%20that%20formula.%20Whether%20it%20be%20yellow%2C%20orange%2C%20or%20red.%20Say%20the%20date%20is%205%2F21%2F18%20the%20color%20of%20the%20cell%20would%20be%26nbsp%3Bred%20because%20it%20is%20within%26nbsp%3B5%20days%20of%20a%20year%20(5%2F21%2F19).%20If%20the%20date%20is%205%2F26%2F18%20it%20would%20be%20orange%20because%20it%20falls%20within%2014%20days%20of%20a%20year%20(5%2F26%2F19).%20If%20it%20is%206%2F5%2F18%20it%20would%20be%20yellow%20because%20it%20falls%20under%2029%20days%20until%20the%20one%20year%20mark%20(6%2F5%2F19).%20And%20if%20it%20is%2030%20or%20more%20(6%2F30%2F18)%20prior%20to%20its%20one%20year%20mark%20(6%2F30%2F2019)%20it%20will%20be%20green.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114251iB01E699818FF784F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22excel%20doc%20snippet.PNG%22%20title%3D%22excel%20doc%20snippet.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20this%20column%20is%20in%20column%20B%20and%20I%20have%202%20more%20columns%20(A%2C%20C).%20However%20this%20format%20only%20applies%20to%20the%20B%20Column.%20Basically%20I%20need%20all%20columns%20to%20change%20color%20based%20on%20the%20date.%20So%20if%20it%20falls%20under%2014%20days%20I%20need%20the%20whole%20row%20to%20change%20color%20to%20orange%20with%20the%20formatted%20cell.%20Right%20now%20if%20the%20date%20changes%20to%20yellow%2C%20red%2C%20orange%2C%20etc.%20column%20B%20will%20change%20to%20the%20appropriate%20color%20but%20columns%20A%20and%20C%20stay%20green.%20Is%20there%20a%20way%20to%20format%20all%203%20columns%20to%20change%20color%20within%20their%20row%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20480px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114252i6CF42A5D9845D060%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%20doc%20snippet2.PNG%22%20title%3D%22excel%20doc%20snippet2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20it%20to%20look%20like%20this%20%5E%5E%5E%5E%20but%20I%20did%20that%20manually.%20Is%20there%20a%20way%20I%20can%20use%20formatting%20to%20get%20it%20to%20do%20this%20automatically%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20want%20them%20to%20be%20connected%20to%20one%20date.%20I%20want%20each%20row%20to%20be%20independent%2C%20but%20I%20want%20the%20columns%20to%20match%20the%20colors%20of%20the%20date%20in%20their%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20prefer%20to%20not%20have%20to%20do%204%20separate%20formats%20for%20143%20rows..%20I%20am%20hoping%20there%20is%20a%20way%20to%20format%20all%20143%20rows%20with%204%20equations.%20and%20not%20572..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%20if%20you%20have%20questions%20please%20ask%20I%20know%20this%20is%20very%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJosh%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-612667%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-612827%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20cells%20based%20on%20another%20cell%20in%20same%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612827%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344429%22%20target%3D%22_blank%22%3E%40joshdoms2%3C%2FA%3E%26nbsp%3B%20I%20assume%20that%20you%20have%20only%20applied%20the%20rule%20to%20a%20set%20cell%20i.e%20%24B3.%20Under%20Manage%20Rules%2C%20expand%20the%20selection%20the%20rule%20applies%20to%2C%20such%20as%20%24A%241%3A%24C%24143%3C%2FP%3E%3C%2FLINGO-BODY%3E
joshdoms2
Occasional Visitor

Hi there,

 

I am working on an excel document and I created a conditional format in order to change the color of a cell based on the date. The formula is: =B3-TODAY()+365>=30

 

What this does is tells the cell if the date falls anytime from the date to 335 days from that date to make the cell green. Then I have it set up within 29 days of 365 days, 14 days, and 5 days and it changes to a different color based on that formula. Whether it be yellow, orange, or red. Say the date is 5/21/18 the color of the cell would be red because it is within 5 days of a year (5/21/19). If the date is 5/26/18 it would be orange because it falls within 14 days of a year (5/26/19). If it is 6/5/18 it would be yellow because it falls under 29 days until the one year mark (6/5/19). And if it is 30 or more (6/30/18) prior to its one year mark (6/30/2019) it will be green.

excel doc snippet.PNG

 

Anyway, this column is in column B and I have 2 more columns (A, C). However this format only applies to the B Column. Basically I need all columns to change color based on the date. So if it falls under 14 days I need the whole row to change color to orange with the formatted cell. Right now if the date changes to yellow, red, orange, etc. column B will change to the appropriate color but columns A and C stay green. Is there a way to format all 3 columns to change color within their row?

excel doc snippet2.PNG

I want it to look like this ^^^^ but I did that manually. Is there a way I can use formatting to get it to do this automatically?

 

I don't want them to be connected to one date. I want each row to be independent, but I want the columns to match the colors of the date in their row.

 

I would prefer to not have to do 4 separate formats for 143 rows.. I am hoping there is a way to format all 143 rows with 4 equations. and not 572..

 

Thank you in advance, if you have questions please ask I know this is very confusing.

 

Josh

 

1 Reply

HI @joshdoms2  I assume that you have only applied the rule to a set cell i.e $B3. Under Manage Rules, expand the selection the rule applies to, such as $A$1:$C$143

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies