# conditional format compare two cells

#### abb16ott

##### New Member
Hi I am really struggling with a conditional format and have tried ABS and not equal to. What I am looking to do is compare two cells
trying to compare cell T3 with T4 and if they are = to each other then fine and if the variance is less/greater that 1.0 then cell turns orange for instance. Both cells need to change colour.

I have tried:-
1) =ABS(T3-T4)>0.5 applied to T3:AG4
2) =ABS(T4-T3)>0.5 applied to T4:AG4

and also tried:-
=\$T4<>\$T1 =>0.5

But no joy and its driving me mad.

Thanks

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Joe4

Welcome to the Board!

You were close! You just need to use the correct absolute reference so that the rows numbers don't change as you move from row 3 to row 4.
So highlight cells T3:AG4 and use this formula:
Code:
``=ABS(T\$3-T\$4)>0.5``

#### abb16ott

##### New Member
Welcome to the Board!

You were close! You just need to use the correct absolute reference so that the rows numbers don't change as you move from row 3 to row 4.
So highlight cells T3:AG4 and use this formula:
Code:
``=ABS(T\$3-T\$4)>0.5``

I presume I should be using "Use a formula to determine which cells to format"? - which I have selected but I am nut sure if its a data problem or not but not all the cells seem to work on the >0.5 formula and I have no idea why?

I am comparing rows 4 to 5 so using =ABS(T\$5-T\$4)>0.5

Charlie

#### Joe4

Can you provide a sample of a set of numbers that is not working?

Also, try these formulas anywhere on the sheet to see what they return:
=ISNUMBER(T4)
=ISNUMBER(T5)

If either returns FALSE, then the data is not in numeric format, and that may be an issue.

Lastly, don't forget to select your formatting option. You may say, "Of course I did that!", but I only mention it because one time a problem had me so vexed that I actually forgot to do that part!

#### abb16ott

##### New Member
Can you provide a sample of a set of numbers that is not working?

Also, try these formulas anywhere on the sheet to see what they return:
=ISNUMBER(T4)
=ISNUMBER(T5)

If either returns FALSE, then the data is not in numeric format, and that may be an issue.

Lastly, don't forget to select your formatting option. You may say, "Of course I did that!", but I only mention it because one time a problem had me so vexed that I actually forgot to do that part!

Both gave a result of True - I am not sure if there is a data issue, formatting issue or what?

Last edited by a moderator:

Replies
3
Views
161
Replies
12
Views
1K
Replies
5
Views
276
Replies
8
Views
500
Replies
1
Views
613

1,190,790
Messages
5,982,928
Members
439,807
Latest member
WXM86

### 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.

### Which adblocker are you using?

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

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