Format if one cell is less than another

russlock

New Member
Joined
Sep 17, 2003
Messages
13
Hi all,
I have tried many ways to do this and have given up.

I have a few customers and I have the spend for each month and I was trying to get the text to highlight if the spend is less than the previous month (if that makes sense).
I have typed how it should look (I have manually changed the text colour)

I hope someone can lead me down the right path. Thanks.

customer jan feb mar
fred 500 354 487
bill 26 200 190

etc..
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,426
Office Version
  1. 365
Platform
  1. Windows
Assuming that the first value (500 in your case) is in B2, then select b2 to last col,last row of data
On home tab, conditional format, new rule,use a formula
=C2<B2
and select your format
 

russlock

New Member
Joined
Sep 17, 2003
Messages
13
Assuming that the first value (500 in your case) is in B2, then select b2 to last col,last row of data
On home tab, conditional format, new rule,use a formula
=C2<B2
and select your format

thanks for this. I tried that and it works for the first cell but then does not for the rest of the row or the data on the next lines
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,426
Office Version
  1. 365
Platform
  1. Windows
Apologies the board stripped out part of the formula it should be
=C2< B2
 

russlock

New Member
Joined
Sep 17, 2003
Messages
13

ADVERTISEMENT

Apologies the board stripped out part of the formula it should be
=C2< B2

still not working for some reason.
did exactly as above and there are months that are clearly higher than the previous month, but still it changes to red and if the formula is correct, it should not change.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,426
Office Version
  1. 365
Platform
  1. Windows
Got it wrong delete the rule, then select C2 to last row/col & use that formula
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,426
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top