Greater than and less than using conditional formatting?

[Lesjoan01]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column H
[/TD]
[TD]Column I
[/TD]
[/TR]
[TR]
[TD]Amount
[/TD]
[TD]18/19 Costs
[/TD]
[/TR]
[TR]
[TD]£90,000.00
[/TD]
[TD]£0.00
[/TD]
[/TR]
[TR]
[TD]£10,000.00
[/TD]
[TD]£0.00
[/TD]
[/TR]
[TR]
[TD]£123,300.00
[/TD]
[TD]£124,000.00
[/TD]
[/TR]
[TR]
[TD]£10,000.00
[/TD]
[TD]£10,000.00
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[tyija1995]

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.

 

[Lesjoan01]

Hi tyija1995,

you legend - that works

Thank you so much

 

[tyija1995]

Glad I could help! Have a good day.

 

[ElvTzh]

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]

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]

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]

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]

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]

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.