highlight cells more or less than 0.2 to nearest 1

CJS548

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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Special-K99

Well-known Member
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?

Last edited:

Eric Golf

Active Member
Hi CJS548,

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

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

Hope this helps,

Eric

Last edited:

CJS548

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

New Member

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

Peter_SSs

MrExcel MVP, Moderator
Try this Conditional Formatting

Book1
B
1
26.09
36.19
46.89
56.79
62.2
73.21
8
910.96
109.8
CF Near
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B10Expression=AND(B2<>"",ABS(B2-ROUND(B2,0))<=0.2)textNO

IT WORKS!!!!
Thanks man!!

Peter_SSs

MrExcel MVP, Moderator
IT WORKS!!!!
Thanks man!!
You're welcome. Thanks for the follow-up.

Replies
2
Views
65
Replies
9
Views
118
Replies
3
Views
115
Replies
6
Views
116
Replies
10
Views
368

1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

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.

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