Conditional Formatting Crash

Hello all,

This is my first post to these forums after being a long time lurker.

I have a long list of large numbers in column Q. The closer the numbers are to each other, the more related their respective rows are to each other. I am trying to figure out a way of highlighting the cells that are within a certain number range of each other. I am attempting to use conditional formatting to do this, and here is one of the formulas I'm using.

Please Login or Register  to view this content.

The value of 305.3 is the difference threshold I'm looking for between any two numbers in the column.

I know what you're thinking. Just sort the column from low to high and compare one number to the next number in the column. I would do that, but this formula is just step one in a more complicated formula I'm going to attempt to create in the future, so I need the formula to work on an unsorted column.

Whenever I run this formula excel locks up and force closes. Is there a better way of doing this? Either with a smarter formula or VBA code or something similar? Any help would be appreciated, and thank you.

That's over 2 million calculations for each cell that the conditional format is applied to
Even the worlds biggest supercomputer is going to cringe at that.

Maybe

Formula:

Please Login or Register  to view this content.

I'm sorry in advance. I think I might be confused here. Is your formula comparing all cells in the column to the first entry? Because I need every cell in the column compared to every other cell in the column, which is really the crushing part of this obviously.

I think it's more that what you're trying to do is a little confusing.

looing at this list of numbers?

50,0,20,40,60,80,100,150,250,200,220,240,260,280,300

Should we highlight everything except 150 with conditional formatting? Because that is how your last post reads.

Let me try to be more clear.

I have a column of numbers. The numbers are all eight digits (including two decimal points) and the column is a list of one thousand to at times a hundred thousand entries in the q column. Here might be some numbers from the list.

Column Q

136261.98 1
214925.68 2
214950.22 3
213000.40 4
185962.32 5
212964.77 6
184000.98 7
182541.06 8

The numbers that are close to each other numerically are more related to each other than those that are more apart. I'm trying to highlight the numbers that are related to each other within a threshold of 305.3. In the above example, I would want the 2nd number and the 3rd number highlighted because they are within + or - 305.3 of each other, and I would want the 4th and the 6th numbers highlighted, because they are within + or - 305.3. It's very difficult because I'm trying to compare each entry in the column to every other entry in the column (until the criteria is met at which point it can stop). For reasons I won't get into unless prodded, I can't sort the column. Is this even possible?

Not sure how well this will respond in calculation times.

Apply this formula as conditional format rule to the entire column.

Formula:

Please Login or Register  to view this content.

Originally Posted by jason.b75

Or a dynamic named range to maximize efficiency.

myrange refers to

Formula:

Please Login or Register  to view this content.

conditional format rule

Formula:

Please Login or Register  to view this content.

but still applied to $Q:$Q.

Originally Posted by Aardigspook

I would recommend amending jason's formula to limit your range a bit. Instead of Q:Q (which is checking the entire Q column 4 times, so 4,193,904 checks!), put in something like $Q$1:$Q$10000 - if your range is up to 10,000 rows. Even if you put in 100,000 as the range, that's still only 400,000 checks, so the calculation will be 10x faster.

Formula:

Please Login or Register  to view this content.

Thank you both so much for your help! Sorry for the late response... the weekend just flew by.