how to identify runs of 6 consecutive numbers in adjacent cells...

[jimmyt83]

Hi, after some help with identifying data in a large spreadsheet. (i'm using excel 2000 XP i think) I've used a formula i found on this site to identify the count of repeat data i have, which is nicely listed from 1 - about 50. Now i want to identify where there occurs runs of over 6 consecutive numbers in adjoining cells and higlight this info.

e.g.
1. 1
2. 2
3. 5
4. 6
5. 7
6. 8
7. 9
8. 10 **HIGHLIGHTED or identified according to the case number
9. 1
10. 6
11. 2
12. 3

hope this is clear...? any help is much appreciated. Thank you, Jim

 

[MickG]

Hi, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jun03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Application.Sum(Dn.Resize(6)) = Dn * 6 + 15 [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(5).Interior.ColorIndex = 6
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jimmyt83]

Thanks very much Mike for your your quick reply...Unfort i get an error message: Runtime error '13':
type mismatch

My data that i want to highligh is actually in column k, so i changed that on your formula...

I am at sea with using macros, just looked up what to do with the code, so this might be a simple error..

Cheers, Jim

 

[jimmyt83]

I mean Mick

 

[MickG]

Hi, Have you got any Text in Column "K", perhaps Header in "K1", if so try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jun49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("K1"), Range("K" & Rows.count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] IsNumeric(Dn) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Application.Sum(Dn.Resize(6)) = Dn * 6 + 15 [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(5).Interior.ColorIndex = 6
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jimmyt83]

Mick, you are a genuis! it took me 10 hours to find that data on the first spreadsheet, and now its taken 10 mins for the second!

Cheers very much! quality!

 

[jimmyt83]

one more query...
if i have filtered the data on multiple custom filters (e.g. column D is greater or eq to 3, and less or eq to 4, Column F is not 0 etc) can i then snip those selected cases out and run the macro on the filtered list?? or else how can i highlight after filtering...

Didn't think of this prob...
Cheers, Jim

 

[MickG]

Hi, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Jun32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("K1"), Range("K" & Rows.count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] IsNumeric(Dn) And Dn.Rows.Hidden = False [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Application.Sum(Dn.Resize(6)) = Dn * 6 + 15 [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(5).Interior.ColorIndex = 6
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick