highlight cells more or less than 0.2 to nearest 1

CJS548

New Member
Joined
Dec 11, 2013
Messages
25
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!
 

Some videos you may like

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.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
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
Joined
Dec 19, 2007
Messages
324
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
Joined
Dec 11, 2013
Messages
25
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
Joined
Dec 11, 2013
Messages
25
Any idea................. o_O
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,212
Messages
5,443,112
Members
405,216
Latest member
joshuapaul

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top