# Greater than and less than using conditional formatting?

#### Lesjoan01

##### New Member
 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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
Hi tyija1995,

you legend - that works

Thank you so much

Glad I could help! Have a good day.

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

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.

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

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.

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

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.

Replies
7
Views
178
Replies
5
Views
310
Replies
1
Views
201
Replies
4
Views
3K
Replies
7
Views
833

Threads
1,207,255
Messages
6,077,311
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back