conditional format compare two cells

[abb16ott]

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

 

[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:

=ABS(T$3-T$4)>0.5

 

[abb16ott]

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:

=ABS(T$3-T$4)>0.5

Thanks for the reply Joe,

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


Thanks for your support

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]

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?

 

[Joe4]

Your image attempt failed. If you wish to post images, you can use the tools mentioned in section B of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

There is also "Test Here" forum which you can use to test posting with those tools before you post in this thread.

Otherwise, just let me know what is in cell T4 and T5.