SOLVED

Excel 2016 help in changing cell colour 'IF'

%3CLINGO-SUB%20id%3D%22lingo-sub-2374765%22%20slang%3D%22en-US%22%3EExcel%202016%20help%20in%20changing%20cell%20colour%20'IF'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374765%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20first%20post%20and%20i'm%20not%20overly%20confident%20with%20excel%2C%20i%20can%20do%20the%20basic%20formulas%20etc.%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20up%20to%2080%20rows%2C%20in%20each%20row%20there%20are%2012%20cells%20(1%20column%20for%20each%20month).%20In%20every%20month%20we%20have%20to%20enter%20the%20weight%20of%20residents.%20I%20need%20the%20cell%20for%20the%20current%20month%20to%20turn%20a%20colour%20(red)%20if%20it%20is%20more%20than%20the%20previous%20months%20figure%20in%20the%20cell%20to%20the%20left%20of%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20can%20anyone%20advise%20how%20i%20can%20do%20this.%20Many%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2374765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374901%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20help%20in%20changing%20cell%20colour%20'IF'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059726%22%20target%3D%22_blank%22%3E%40Hannah_B142%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20first%20month%20is%20in%20column%20B%2C%20and%20the%20last%20in%20column%20M%20(with%20names%20in%20column%20A).%3C%2FP%3E%0A%3CP%3EAnd%20let's%20say%20the%20data%20begin%20in%20row%202%20(with%20headers%20in%20row%201).%3C%2FP%3E%0A%3CP%3ESelect%20C2%3AM80.%3C%2FP%3E%0A%3CP%3EC2%20should%20be%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%26nbsp%3B%26nbsp%3B%20%3DB2%26nbsp%3B%26nbsp%3B%20in%20the%20box%20next%20to%20it.%20B2%20is%20the%20cell%20to%20the%20left%20of%20the%20top%20left%20cell%20of%20the%20selection.%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-2375726%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20help%20in%20changing%20cell%20colour%20'IF'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20but%20this%20does%20not%20appear%20to%20work.%20It%20could%20be%20that%20i'm%20doing%20something%20wrong%20but%20i%20don't%20think%20so.%3C%2FP%3E%3CP%3ECells%20are%20all%20set%20to%20number%20format%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375756%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20help%20in%20changing%20cell%20colour%20'IF'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375756%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059726%22%20target%3D%22_blank%22%3E%40Hannah_B142%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20write%20%22%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3Eselect%3C%2FFONT%3E%3C%2FSTRONG%3E%20a%20formula%20to%20determine%20which%20cells%20to%20format%22%20but%20%22%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3EUse%3C%2FSTRONG%3E%3C%2FFONT%3E%20a%20formula%20to%20determine%20which%20cells%20to%20format%22%2C%20exactly%20what's%20in%20the%20Conditional%20Formatting%20New%20Rule%20dialog%20box%20(last%20option)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20299px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282654i70B3651339828450%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all

 

This is my first post and i'm not overly confident with excel, i can do the basic formulas etc.

I have a spreadsheet with up to 80 rows, in each row there are 12 cells (1 column for each month). In every month we have to enter the weight of residents. I need the cell for the current month to turn a colour (red) if it is more than the previous months figure in the cell to the left of it. 

Please can anyone advise how i can do this. Many thanks

9 Replies
best response confirmed by Hannah_B142 (Occasional Contributor)
Solution

@Hannah_B142 

Let's say the first month is in column B, and the last in column M (with names in column A).

And let's say the data begin in row 2 (with headers in row 1).

Select C2:M80.

C2 should be 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   =B2   in the box next to it. B2 is the cell to the left of the top left cell of the selection.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

@Hannah_B142 

I need the cell for the current month...

 

Demo.png

  • In B1-M1 enter dates (1/1/21, 2/1/21...12/1/21) then format them as you like
  • Select C2:M80 > Home (tab) > Cond. Format > New Rule > Use a formula to determine which cells to format
  • Enter formula: =AND(MONTH(C$1) = MONTH(TODAY()), C2 > B2)
  • Format... > Font > Color > Select a color > OK twice

@Hans Vogelaar 

 

Thank you but this does not appear to work. It could be that i'm doing something wrong but i don't think so.

Cells are all set to number format too.

Hi L

Please can you break this down either further. As in what do you mean by "select a formula to determine which cells to format" please. Thanks

@Hannah_B142 

I didn't write "select a formula to determine which cells to format" but "Use a formula to determine which cells to format", exactly what's in the Conditional Formatting New Rule dialog box (last option):

Demo.png

@Hannah_B142 

 

It appears that L.z. and I interpreted your question differently.

By "I need the cell for the current month to turn a colour (red) if it is more than the previous months figure in the cell to the left of it. ", do you meant that you only want to colour some cells in the column for May 2021? Or in the column for any mointh if its value is larger than that in the month before?

I need the current cell to change colour if it is greater than the cell to the left of it. But having said that, if in 2 months time (we only do this once per month),the number increases again, i still want the previous cell of 2 months ago to remain red. Basically, we need to track the data and over the period of a year be easily able to identify any increases. I hope this makes sense. Thanks

@Hannah_B142 

Try the steps in my first reply.

Hi Hans
I've done it, thank you so much.
Hannah