VBA to Check overlapping numbers.

[rjnewsome]

I have a sheet which contains a range (G6:AM87). Users can enter numbers into this range. These numbers do NOT necessarily fill the range.

I am looking for a VBA script to analyze those cells which contain numbers to ensure every number is at least 8 digits apart from any other number in said range.
If all numbers are spaced 8 digits or more the range containing data is colored green. However if two numbers are less than 8 digits apart they get colored red.

For example:

If the range contains the numbers 8 and 15 they would be colored red because they overlap less than 8 digits.


Hope this helps define what I need.


Thanks for any and all assistance!

~Rob

[xladept]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[shg]

Why not just use conditional formatting?

=COUNTIFS($G$6:$J$14, ">=" & G6-7, $G$6:$J$14, "<=" & G6+7)>1

[rjnewsome]

Thanks for the responses. The conditional format was near what I needed but this may explain more of what I need.

Thanks again!
~Rob

Example.xlsm

[xladept]

Nevermind

[rjnewsome]

Nevermind

xladept,

Too complicated or is it not even possible using Excel?

[rjnewsome]

I'm getting a circular reference error with this in Cell if statement. Can someone see if they can clean it up for me?

=IF('Shot Diagram '!G7='Shot Diagram '!$B$14,'Shot Diagram '!G6+'Shot Diagram '!$R$61:$S$61,IF('Shot Diagram '!G7='Shot Diagram '!$B$15,'Shot Diagram '!G6+'Shot Diagram '!$R$61:$S$61,IF('Shot Diagram '!G7='Shot Diagram '!$B$16,'Shot Diagram '!G6+'Shot Diagram '!$T$61:$U$61,IF('Shot Diagram '!G7='Shot Diagram '!$B$17,AND(Sheet2!C7='Shot Diagram '!G6+'Shot Diagram '!$V$64:$W$64,Sheet2!C8='Shot Diagram '!G6+'Shot Diagram '!$V$65:$W$65,Sheet2!C9='Shot Diagram '!G6+'Shot Diagram '!$V$66:$W$66,Sheet2!C10='Shot Diagram '!G6+'Shot Diagram '!$V$67:$W$67),IF('Shot Diagram '!G6='Shot Diagram '!$B$18,AND(Sheet2!C7='Shot Diagram '!G6+'Shot Diagram '!$Y$64:$Z$64,Sheet2!C9='Shot Diagram '!G6+'Shot Diagram '!$Y$65:$Z$65,Sheet2!C10='Shot Diagram '!G6+'Shot Diagram '!$Y$66:$Z$66,Sheet2!C11='Shot Diagram '!G6+'Shot Diagram '!$Y$67:$Z$67),)))))

Thanks,

[xladept]

Hi RJ,

I think it's too complicated - is there a range of permissible numeric entries??

[rjnewsome]

Hi RJ,

I think it's too complicated - is there a range of permissible numeric entries??

No its essentially a timing sequence. The user enters the delay between two points then the first value (starting point) is added to the user entered delay time, and applied to the linked point.

Eg: Point A's value is 42. User links it to point B with a 17. Point B becomes 42 +17 = 59 and so on and so forth. I just need to check that, any point - any other point > 7.


hope that clears things up a bit.