SOLVED

# Excel 2016 help in changing cell colour 'IF'

Occasional Contributor

# Excel 2016 help in changing cell colour 'IF'

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.

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

# Re: Excel 2016 help in changing cell colour 'IF'

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.

# Re: Excel 2016 help in changing cell colour 'IF'

I need the cell for the current month...

• 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

# Re: Excel 2016 help in changing cell colour 'IF'

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.

# Re: Excel 2016 help in changing cell colour 'IF'

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

# Re: Excel 2016 help in changing cell colour 'IF'

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):

# Re: Excel 2016 help in changing cell colour 'IF'

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?

# Re: Excel 2016 help in changing cell colour 'IF'

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

# Re: Excel 2016 help in changing cell colour 'IF'

Try the steps in my first reply.

# Re: Excel 2016 help in changing cell colour 'IF'

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