Greater than and less than using conditional formatting?

Lesjoan01

New Member
Joined
Mar 29, 2017
Messages
19
Column H
Column I
Amount
18/19 Costs
£90,000.00
£0.00
£10,000.00
£0.00
£123,300.00
£124,000.00
£10,000.00
£10,000.00

<tbody>
</tbody>

I need to highlight the above cells as follows:

If the amount in Column H is higher than the amount in Column I then the cell needs to be filled Red
If the amount in Column H is lower than the amount in Column I then the cell needs to green
If the amount in Column H and Column I are the same then no formatting is to be applied

Any ideas please?

Many thanks
Lesley
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
Hi Lesjoan01,

I'd highlight the first value (H2) then go to conditional formatting and set up a new rule -> Use a formula to determine which cells to format.

Then in the "Format values where this formula is true:" section put
=$H2>$I2
Click Format -> Fill -> Red
Click Ok

Now make a 2nd rule on the same value (H2)
=$H2<$I2
Click Format -> Fill -> Green

Now highlight cell H2 and use the format painter down the H column.
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
Glad I could help! Have a good day.
 

ElvTzh

New Member
Joined
Mar 6, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi tyija1995,
I need help on the below. Is there any "IF" function to that I can use to check the below price?

Example:
Raw Cost 1 is always the based or guidelines that Raw Cost 2 < Raw Cost 3 < Raw Cost 4 < Raw Cost 5 cannot be higher than each other.
Shall either column is higher than 1 and another, it'll be shaded with RED means as error

Raw Cost #1 (Conv.) Raw Cost #2 (Conv.) Raw Cost #3 (Conv.) Raw Cost #4 (Conv.) Raw Cost #5 (Conv.)
$ 0.00180 $ 0.00180 $ 0.00180 $ 0.00180 $ 0.00180
$ 0.00140 $ 0.00140 $ 0.00140 $ 0.00140 $ 0.00140
$ 0.01622 $ 0.01622 $ 0.01622 $ 0.01622 $ 0.01622
$ 0.05400 $ 0.05400 $ 0.05400 $ 0.05400 $ 0.05400
$ 0.00206 $ 0.00206 $ 0.00206 $ 0.00206 $ 0.00206
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,206
Office Version
  1. 365
Platform
  1. Windows
I need help on the below.
Welcome to the MrExcel board!

What you want is not clear to me.

Can you make up a small set of sample data that would have some highlighted cells, colour them manually and post it here with XL2BB so we can see the colour and copy the data for testing. Also give another explanation in relation to that new data.
 

ElvTzh

New Member
Joined
Mar 6, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!

What you want is not clear to me.

Can you make up a small set of sample data that would have some highlighted cells, colour them manually and post it here with XL2BB so we can see the colour and copy the data for testing. Also give another explanation in relation to that new data.

Hi Peter_SSs,
Sorry, i wasn't aware that my unit price are all the same. Below here is the revised one.

The correct method is based on the 1st line... which is Raw Cost 1 > Raw Cost 2 > Raw Cost 3 > Raw Cost 4 > Raw Cost 5

I am seeking for a IF formula to find the second line error
example: Raw Cost 2 > Raw Cost 1 and the Raw Cost 4 < Raw Cost 5

Raw Cost #1 (Conv.) Raw Cost #2 (Conv.) Raw Cost #3 (Conv.) Raw Cost #4 (Conv.) Raw Cost #5 (Conv.)
$ 0.00180 $ 0.00170 $ 0.00160 $ 0.00150 $ 0.00140
$ 0.00140 $ 0.00150 $ 0.00140 $ 0.00130 $ 0.00140
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,206
Office Version
  1. 365
Platform
  1. Windows
Why is $ 0.00130 highlighted in the 2nd row? It is less than the number to its left. In Fact is is less than all numbers to its left.
 

ElvTzh

New Member
Joined
Mar 6, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Why is $ 0.00130 highlighted in the 2nd row? It is less than the number to its left. In Fact is is less than all numbers to its left.

Hi Peter,
If you're looking at the 1st row...
the correct method is Raw Cost 1 greater than Raw Cost 2 greater than Raw Cost 3 greater than Raw Cost 4 greater than Raw Cost 5 - which Raw Cost 5 is supposed to be the lowest or an equivalent price to Raw Cost 4. (Descending)

Where if there is any price in between the Raw Cost 1 to 5 is higher, not in descending mode that means the price is incorrect.
Raw Cost #1 (Conv.) Raw Cost #2 (Conv.) Raw Cost #3 (Conv.) Raw Cost #4 (Conv.) Raw Cost #5 (Conv.)
$ 0.00140 $ 0.00150 $ 0.00140 $ 0.00130 $ 0.00140
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,206
Office Version
  1. 365
Platform
  1. Windows
Where if there is any price in between the Raw Cost 1 to 5 is higher, not in descending mode that means the price is incorrect.
Raw Cost #1 (Conv.)Raw Cost #2 (Conv.)Raw Cost #3 (Conv.)Raw Cost #4 (Conv.)Raw Cost #5 (Conv.)
$ 0.00140$ 0.00150$ 0.00140$ 0.00130$ 0.00140
That still does not explain (at least I cannot understand it) why Raw Cost #2 and Raw Cost #4 are bold and the others are not. Please step through the logic step by step in detail, one column at a time.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top