highlight cells more or less than 0.2 to nearest 1

[CJS548]

Hi,
is there any way to highlight the cells in a range, if:
the value is more or less than 0.2 to the nearest 1
there are already a formula in each of those cells in the range, which will change according to what i change at the other cell.

example:
if cell value = 6.09, highlight
if cell value = 6.19, highlight
if cell value = 6.89, highlight
if cell value = 6.79, no highlight
if cell value = 2.20, highlight
if cell value = 3.21, no highlight
if cell value = 10.96, highlight
meaning if it is 0.2 more or less than the nearest one, it will get highlighted, if not, then no highlight.
the range is $Y$7:$AC$10

vba can be applied.
thanks!

 

[Special-K99]

What do you mean by "nearest one"?

I'm looking at cell B2.
The nearest one to it is B1.
And A2.
And C2.
And B3.
And diagonally A1, C1, A3 and C3.

So which cell are we talking about?

 

[Eric Golf]

Hi CJS548,

If you mean within 0.2 of a whole number, then try conditional formatting with the below:-

=OR(MOD(Y7,1)>=0.8,MOD(Y7,1)<=0.2)

Hope this helps,

Eric

 

[CJS548]

Sorry I didn't make it clear...
So, a number after rounding up or rounding down to no decimal, is my so called "nearest 1"
So, if the cells gives a value that is variance by 0.2 to its nearest zero decimal, then highlight.
for example,
if cells = 19.13, highlight, coz its within 0.2 from 19
if cells = 15.92, highlight, coz its within 0.2 from 16
if cells = 7.36, don't highlight, coz its more than 0.2 from 7
Hope it is clear now...
the range for this to apply is $Y$7:$AC$10
Thanks

 

[CJS548]

Any idea.................

 

[Peter_SSs]

Try this Conditional Formatting

Book1
	B
1
2	6.09
3	6.19
4	6.89
5	6.79
6	2.2
7	3.21
8
9	10.96
10	9.8
CF Near

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(B2<>"",ABS(B2-ROUND(B2,0))<=0.2)	text	NO

 

[CJS548]

IT WORKS!!!!
Thanks man!!

 

[Peter_SSs]

IT WORKS!!!!
Thanks man!!

You're welcome. Thanks for the follow-up.