SOLVED

Conditional Formatting - Multiple Months

%3CLINGO-SUB%20id%3D%22lingo-sub-2428098%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20-%20Multiple%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428098%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20conditionally%20format%20a%20column%20in%20this%20file%20that%20will%20turn%20the%20cells%20in%20column%20J%20red%20for%20less%20than%203%20month%2C%20yellow%20for%203-6%20months%2C%20and%20green%20for%209%2B%20months%20based%20off%20the%20monthly%20usage%20in%20column%20I.%20(i.e.%20column%20I%20has%20monthly%20usage%20of%20300%20but%20column%20J%20OQS%20shows%2010%2C%20the%20cell%20would%20turn%20red).%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20or%20feedback%20is%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2428098%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-2428135%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Multiple%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074042%22%20target%3D%22_blank%22%3E%40AmandaLittle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20about%206%20to%209%20months%3F%20You%20can%20create%20an%20extra%20rule%20for%20that%20if%20necessary.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20J2%3AJ6%2C%20J2%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3ESet%20the%20fill%20color%20to%20red.%20This%20will%20be%20the%20default.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20home%20tab%20of%20the%20ribbon%2C%20select%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%20or%20equal%20to'%20from%20the%20second%20dropdown.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3D3*I2%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%20yellow.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20these%20steps%20but%20with%20the%20formula%20%3D9*I2%20and%20green%20as%20fill%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2428226%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Multiple%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428226%22%20slang%3D%22en-US%22%3EHans%20Vogelaar%20that%20works%2C%20thank%20you.%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I need to conditionally format a column in this file that will turn the cells in column J red for less than 3 month, yellow for 3-6 months, and green for 9+ months based off the monthly usage in column I. (i.e. column I has monthly usage of 300 but column J OQS shows 10, the cell would turn red). 

Any help or feedback is appreciated.

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@AmandaLittle 

What about 6 to 9 months? You can create an extra rule for that if necessary.

 

Select J2:J6, J2 should be the active cell in the selection.

Set the fill color to red. This will be the default.

 

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.

Enter the formula =3*I2 in the box next to it.

Click Format...

Activate the Fill tab.

Select yellow.

Click OK, then OK.

 

Repeat these steps but with the formula =9*I2 and green as fill color.

Hans Vogelaar that works, thank you.