Show numbers nearly the same

[hendrikbez]

Is this possible.

I have a number that can change very time eg 0.00001234
Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004, 0.00000008, 0.00000016 (goes up by double each time.

I need that if the number on top (0.00001234) is about 100 less (0.00001134) or 100 (0.00001334) more, it must show that number in a color

is this possible

 

[ChrisFoster]

Yes, you can use conditional formatting to do that. It will allow you to change the colour for numbers between a specified range.

 

[hendrikbez]

How will conditional formatting work on this.

The number can change in line Amount 0.00001771. So I want the numbers to be like the red on below when the Amount is 0.00001771, 100 less or 100 more, if I change the Amount number, the color must change to the next high or low number.

Amount 0.000001771

1 0,00000001 0,00000002 0,00000003 0,00000004 0,00000005 0,00000010 0,00000020 0,00000030 0,00000040 0,00000050 0,00000100 0,00000200 0,00000300 0,00000400
2 0,00000002 0,00000004 0,00000006 0,00000008 0,00000010 0,00000020 0,00000040 0,00000060 0,00000080 0,00000100 0,00000200 0,00000400 0,00000600 0,00000800
3 0,00000004 0,00000008 0,00000012 0,00000016 0,00000020 0,00000040 0,00000080 0,00000120 0,00000160 0,00000200 0,00000400 0,00000800 0,00001200 0,00001671
4 0,00000008 0,00000016 0,00000024 0,00000032 0,00000040 0,00000080 0,00000160 0,00000240 0,00000320 0,00000400 0,00000800 0,00001881 0,00002400 0,00003200
5 0,00000016 0,00000032 0,00000048 0,00000064 0,00000080 0,00000160 0,00000320 0,00000480 0,00000640 0,00000800 0,00001671 0,00003200 0,00004800 0,00006400
6 0,00000032 0,00000064 0,00000096 0,00000128 0,00000160 0,00000320 0,00000640 0,00000960 0,00001280 0,00001671 0,00003200 0,00006400 0,00009600 0,00012800
7 0,00000064 0,00000128 0,00000192 0,00000256 0,00000320 0,00000640 0,00001280 0,00001871 0,00002560 0,00003200 0,00006400 0,00012800 0,00019200 0,00025600
8 0,00000128 0,00000256 0,00000384 0,00000512 0,00000640 0,00001280 0,00002560 0,00003840 0,00005120 0,00006400 0,00012800 0,00025600 0,00038400 0,00051200
9 0,00000256 0,00000512 0,00000768 0,00001024 0,00001280 0,00002560 0,00005120 0,00007680 0,00010240 0,00012800 0,00025600 0,00051200 0,00076800 0,00102400

 

[Fluff]

Maybe something like

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	0.00001771
3
4	0.00000001	0.00000002	0.00000003	0.00000004	0.00000005	0.00000010	0.00000020	0.00000030	0.00000040	0.00000050	0.00000100	0.00000200	0.00000300	0.00000400
5	0.00000002	0.00000004	0.00000006	0.00000008	0.00000010	0.00000020	0.00000040	0.00000060	0.00000080	0.00000100	0.00000200	0.00000400	0.00000600	0.00000800
6	0.00000004	0.00000008	0.00000012	0.00000016	0.00000020	0.00000040	0.00000080	0.00000120	0.00000160	0.00000200	0.00000400	0.00000800	0.00001200	0.00001671
7	0.00000008	0.00000016	0.00000024	0.00000032	0.00000040	0.00000080	0.00000160	0.00000240	0.00000320	0.00000400	0.00000800	0.00001881	0.00002400	0.00003200
8	0.00000016	0.00000032	0.00000048	0.00000064	0.00000080	0.00000160	0.00000320	0.00000480	0.00000640	0.00000800	0.00001671	0.00003200	0.00004800	0.00006400
9	0.00000032	0.00000064	0.00000096	0.00000128	0.00000160	0.00000320	0.00000640	0.00000960	0.00001280	0.00001671	0.00003200	0.00006400	0.00009600	0.00012800
10	0.00000064	0.00000128	0.00000192	0.00000256	0.00000320	0.00000640	0.00001280	0.00001871	0.00002560	0.00003200	0.00006400	0.00012800	0.00019200	0.00025600
11	0.00000128	0.00000256	0.00000384	0.00000512	0.00000640	0.00001280	0.00002560	0.00003840	0.00005120	0.00006400	0.00012800	0.00025600	0.00038400	0.00051200
12	0.00000256	0.00000512	0.00000768	0.00001024	0.00001280	0.00002560	0.00005120	0.00007680	0.00010240	0.00012800	0.00025600	0.00051200	0.00076800	0.00102400
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:N12	Expression	=AND(A4>=$A$2-0.000001,A4<=$A$2+0.000001)	text	NO

 

[hendrikbez]

Hi Fluff, yes like this, I will look if I can get it to work.
Thank you

 

[Peter_SSs]

Same concept, CF formula written a different way

20 07 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	0.00001771
3
4	0.00000001	0.00000002	0.00000003	0.00000004	0.00000005	0.0000001	0.0000002	0.0000003	0.0000004	0.0000005	0.000001	0.000002	0.000003	0.000004
5	0.00000002	0.00000004	0.00000006	0.00000008	0.0000001	0.0000002	0.0000004	0.0000006	0.0000008	0.000001	0.000002	0.000004	0.000006	0.000008
6	0.00000004	0.00000008	0.00000012	0.00000016	0.0000002	0.0000004	0.0000008	0.0000012	0.0000016	0.000002	0.000004	0.000008	0.000012	0.00001671
7	0.00000008	0.00000016	0.00000024	0.00000032	0.0000004	0.0000008	0.0000016	0.0000024	0.0000032	0.000004	0.000008	0.00001881	0.000024	0.000032
8	0.00000016	0.00000032	0.00000048	0.00000064	0.0000008	0.0000016	0.0000032	0.0000048	0.0000064	0.000008	0.00001671	0.000032	0.000048	0.000064
9	0.00000032	0.00000064	0.00000096	0.00000128	0.0000016	0.0000032	0.0000064	0.0000096	0.0000128	0.00001671	0.000032	0.000064	0.000096	0.000128
10	0.00000064	0.00000128	0.00000192	0.00000256	0.0000032	0.0000064	0.0000128	0.00001871	0.0000256	0.000032	0.000064	0.000128	0.000192	0.000256
11	0.00000128	0.00000256	0.00000384	0.00000512	0.0000064	0.0000128	0.0000256	0.0000384	0.0000512	0.000064	0.000128	0.000256	0.000384	0.000512
12	0.00000256	0.00000512	0.00000768	0.00001024	0.0000128	0.0000256	0.0000512	0.0000768	0.0001024	0.000128	0.000256	0.000512	0.000768	0.001024
CF Close

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:N12	Expression	=ABS($A$2-A4)<=0.000001	text	NO

 

[hendrikbez]

Hi I am looking at this, but do not know what to do,
I click the button Conditional Formatting, but which one do I use, when creating new rule.

 

[Peter_SSs]

For the layout shown in Fluff's and my posts,

1. Select from A4 down to N12
2. Home ribbon tab -> Conditional Formatting -> New Rule .. -> Use a formula to determine which cells to format -> Format values where this formula is true: -> Enter the formula suggested in that box -> Click 'Format...' -> Fill tab -> Choose colour -> OK -> OK

 

[hendrikbez]

I think I have got it, cannot add file to show it.
The only thing is the the number in A2 will change every few minutes, how do I get it to update the rest of the numbers to do the same.

 

[Peter_SSs]

You cannot attach an actual file. However, you can use XL2BB to show part of your sheet like Fluff & I have done.

The highlights will change accordingly automatically every time the value in cell A2 changes. For example, this is the exact sheet I showed above and all I have done is change the value in A2.

20 07 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	0.00000164
3
4	0.00000001	0.00000002	0.00000003	0.00000004	0.00000005	0.0000001	0.0000002	0.0000003	0.0000004	0.0000005	0.000001	0.000002	0.000003	0.000004
5	0.00000002	0.00000004	0.00000006	0.00000008	0.0000001	0.0000002	0.0000004	0.0000006	0.0000008	0.000001	0.000002	0.000004	0.000006	0.000008
6	0.00000004	0.00000008	0.00000012	0.00000016	0.0000002	0.0000004	0.0000008	0.0000012	0.0000016	0.000002	0.000004	0.000008	0.000012	0.00001671
7	0.00000008	0.00000016	0.00000024	0.00000032	0.0000004	0.0000008	0.0000016	0.0000024	0.0000032	0.000004	0.000008	0.00001881	0.000024	0.000032
8	0.00000016	0.00000032	0.00000048	0.00000064	0.0000008	0.0000016	0.0000032	0.0000048	0.0000064	0.000008	0.00001671	0.000032	0.000048	0.000064
9	0.00000032	0.00000064	0.00000096	0.00000128	0.0000016	0.0000032	0.0000064	0.0000096	0.0000128	0.00001671	0.000032	0.000064	0.000096	0.000128
10	0.00000064	0.00000128	0.00000192	0.00000256	0.0000032	0.0000064	0.0000128	0.00001871	0.0000256	0.000032	0.000064	0.000128	0.000192	0.000256
11	0.00000128	0.00000256	0.00000384	0.00000512	0.0000064	0.0000128	0.0000256	0.0000384	0.0000512	0.000064	0.000128	0.000256	0.000384	0.000512
12	0.00000256	0.00000512	0.00000768	0.00001024	0.0000128	0.0000256	0.0000512	0.0000768	0.0001024	0.000128	0.000256	0.000512	0.000768	0.001024
CF Close

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:N12	Expression	=ABS($A$2-A4)<=0.000001	text	NO