Conditional Formatting of 2 x cells based on very close match?

[westham]

Hi all

Been trying to find a solution to this without success and hope someone can help here please. I am trying to find a way to use conditional formatting to highlight a close match between 2 x cells. For example below I would like cells A1 and B1 and A2 and B2 to format as green as the match is so close but the 3rd row to remain as either unformatted or red as the gap between A3 and B3 is more than 1. I have tried rounding etc. but can't see to get this to work. Is there a way to input something in the 'Use a formula to determine which cells to format' perhaps that would enable both to be formatted as green if they are a close match (less than 1)?

Many thanks

	A	B
1	627.02	627.01
2	159.99	160.00
3	156.32	155.22

 

[Muhammad_Usman]

1235.xlsm
	A	B
1	627.02	627.01
2	159.99	160
3	156.32	155.22
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B3	Expression	=ABS($A1-$B1)<=0.05	text	NO

 

[westham]

Hi, thanks for this - am I doing something wrong as when I use this B1 is formatted but it leaves A1 untouched? Ideally I would like both to be formatted with green fill if both numbers are close.

Many thanks

 

[Joe4]

Hi, thanks for this - am I doing something wrong as when I use this B1 is formatted but it leaves A1 untouched? Ideally I would like both to be formatted with green fill if both numbers are close.

Many thanks

Did you select BOTH columns A and B when setting up the Conditional Formatting Rule?
Did you keep the "$" signs in the formula, i.e.

=ABS($A1-$B1)<=0.05

If you did both of these things, columns A and B will ALWAYS be formatted the same (it will be impossible for them NOT to be formatted the same).

 

[westham]

Did you select BOTH columns A and B when setting up the Conditional Formatting Rule?
Did you keep the "$" signs in the formula, i.e.

=ABS($A1-$B1)<=0.05

If you did both of these things, columns A and B will ALWAYS be formatted the same (it will be impossible for them NOT to be formatted the same).

Brilliant, thanks both - I didn't think I needed the $ as I was comparing just the 2 x cells for my test. Adding this and it works perfectly - thanks again

 

[Joe4]

Brilliant, thanks both - I didn't think I needed the $ as I was comparing just the 2 x cells for my test. Adding this and it works perfectly - thanks again

Yes, the reason why you need them is because you are formatting two different columns at once!
When you enter a Conditional Formatting formula for a multi-cell range, Excel applies the formula to the very FIRST cell in your range.
And like all other Excel formulas, Excel will automatically adjust them for the other cells in your range.

So, without the $, the formula applied to cell A1 would be:

 =ABS(A1-B1)<=0.05

but the formula applied to cell B1 would then be:

 =ABS(B1-C1)<=0.05

as Excel will shift all column references in your formula by one column for every one column we move over.
Make sense?

That is why we need to lock those columns down.
If you were only apply the CF rule to a single column, you would not need them, but since you are applying to two columns, you do need them.

 

[westham]

It does make sense, thanks Joe. One last question if I may please - to apply this rule to further rows do I need to enter it for each selection or is there a way to go down each column so the rule simply compares A1-B1, A2-B2, A3-B3 etc.?

 

[Joe4]

It does make sense, thanks Joe. One last question if I may please - to apply this rule to further rows do I need to enter it for each selection or is there a way to go down each column so the rule simply compares A1-B1, A2-B2, A3-B3 etc.?

As I said, Excel will automatically adjust the formula for all the other cells in your range.
Since we have NOT put a $ in front of the row number, those will change to match the row number.

So all you need to do is to select the entire range (i.e. A1:B100) that you want to apply the CF formula to before you enter the formula.
I recommend choosing some row number large enough that it will cover any new information you add in at a later time.