Macro to identify a trend

FDibbins.. Thanks for the quick response...

You raised a valid question... But what I was trying to ask is.. I have 3 values I enter.. ie 3000, 3500 & 2500

The macro then scans each cell looking for a match based on those I entered but within a variance of 500.. Not a run of values <> 500.. so it would find in the one week the values 3232,3615 & 2777.
Even if it finds 1, 2 or 3 close matches (within the 500 variance).. more the better..

Make sense..

Hi

In the range L3:L5 enter your 3 values (3000, 3500 and 2500). Then enter 500 in M2
Highlight the range C3:I8 and then add 3 conditional formatting formulas
=ABS(C3-$L$4) <=$M$2
=ABS(C3-$L$3) <=$M$2
=ABS(C3-$L$2)<=$M$2
with different fill formats as required.

This should highlight the relevant cells, and if you have different format colors, may also give you an indication which value it matches.

HTH

rylo

Thanks Rylo.. Ill give this a shot.... You guys make it look so easy...

Originally Posted by rylo

Hi

In the range L3:L5 enter your 3 values (3000, 3500 and 2500). Then enter 500 in M2
Highlight the range C3:I8 and then add 3 conditional formatting formulas
=ABS(C3-$L$4) <=$M$2
=ABS(C3-$L$3) <=$M$2
=ABS(C3-$L$2)<=$M$2
with different fill formats as required.

This should highlight the relevant cells, and if you have different format colors, may also give you an indication which value it matches.

HTH

rylo

Originally Posted by Witsendestate

Thanks Rylo.. Ill give this a shot.... You guys make it look so easy...

Rylo & Team.... Thanks so much for your help.. Rylo's solution works perfectly..

Team... Rylo's solution was perfect.. I now want to expand on it please..

I would like to find 3 consecutive numbers in a row... The answer from Rylo will highlight each of the cells with an individual match but I would like to find if any cells next to each other fall within the variance...

Your thoughts..

This is what I tried:-

=ABS(C6:I64-$N$4) <=$M$2 and <=$M$3 and <=$M$4

The error I get is :- You may not use Unions, Intersections or Array Constants for Conditional Formatting

Hi

Can you put up an example file, with some data and your comparison values and the comparison range, and the conditional formatting formulas from above (if they are still relevant) and advise which cells you would want highlighted and the reason for their highlighting.

rylo

Rylo,

Here you go.. Your solution does work but just wondering if there is a single formula to do this instead of having multiple formula's..

The spreadsheet attached cells c7,d7,e7 are highlighted as they fall in the range of the values given.

Im also interested in having a "or" condition so it highlights 2 matches or 3 matches.

Mark

Hi

But do you want the range C7:E7 to be in a different color as there are 3 contiguous cells, or do you really just want to have one formula that covers off the 3 possible conditions?

rylo

Huh.. I sound like a typical end user:

Both please.. ;-)

If there are 3 in a row use color X, 2 in a row use color Y, 1 only use color Z.

or

1 Formula with 3 possible conditions...

Mark

Hi

using your example file from #10, remove the conditional formatting formulas, then try

Please Login or Register  to view this content.

Is this the sort of result you were looking for?


rylo

Rylo...

I think we need to change your status from Guru to Legend.... It worked like a charm... Huge thankyou for your time and knowledge.

Mark