Formatting cells based on another cell in same row

Copper Contributor

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

 

2 Replies

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

I was excited to see the same question I have was asked... disappointed that no real answer was given.

Did you ever figure this out?