Highlight Rows When Out Of Sequence

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,714
Office Version
  1. 365
Platform
  1. Windows
I have sheet 1. The code needs to look at the data in Column C (Column A below) when the data is the same it needs to look at the data in AI is in order and highlight the rows if not. The first example I have highlighted the rows that are not in sequence. Column AI should have all 'Front Suspension' first then all 'Rear Suspension'.

The second example shows what they should look like.

If a code could be written for me i would be very grateful.

Thanks.

Excel 2010
AAI
1Header1Header33
2ABC123Front Suspension
3ABC123Rear Suspension
4ABC123Front Suspension
5ABC123Rear Suspension
6ABC123Front Suspension
7ABC123Rear Suspension
8DEF123Front Suspension
9DEF123Front Suspension
10DEF123Front Suspension
11DEF123Rear Suspension
12ABC234Rear Suspension
13ABC234Front Suspension

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



This is what they should look like.

Excel 2010
AAI
1Header1Header33
2ABC123Front Suspension
3ABC123Front Suspension
4ABC123Front Suspension
5ABC123Rear Suspension
6ABC123Rear Suspension
7ABC123Rear Suspension
8DEF123Front Suspension
9DEF123Front Suspension
10DEF123Front Suspension
11DEF123Rear Suspension
12ABC234Front Suspension
13ABC234Rear Suspension

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Please note I don't need them sorted to look like the second example, just coloured to warn me like the first example.
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
if you sorted by the first column ascending and the second column ascending they would all be in perfect order, and providing there are no blank rows or columns, all other columns would sort correctly.
 
Upvote 0
Thanks but that's no good. There is far too much criteria to be met and over 100,000 rows per file. As I said no sort is needed just a row colouring to advise me.
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG04Jun07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, G [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] G [COLOR="Navy"]In[/COLOR] .Item(K).Offset(, 32)
    [COLOR="Navy"]If[/COLOR] Not R = 2 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] G = "Rear Suspension" [COLOR="Navy"]Then[/COLOR] R = 1
        [COLOR="Navy"]If[/COLOR] R = 1 And G = "Front Suspension" [COLOR="Navy"]Then[/COLOR] R = 2
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] G
 [COLOR="Navy"]If[/COLOR] R = 2 [COLOR="Navy"]Then[/COLOR] .Item(K).EntireRow.Interior.ColorIndex = 6: R = 0
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG04Jun07
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, G [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] .Item(K).Offset(, 32)
    [COLOR=Navy]If[/COLOR] Not R = 2 [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] G = "Rear Suspension" [COLOR=Navy]Then[/COLOR] R = 1
        [COLOR=Navy]If[/COLOR] R = 1 And G = "Front Suspension" [COLOR=Navy]Then[/COLOR] R = 2
    [COLOR=Navy]End[/COLOR] If
 [COLOR=Navy]Next[/COLOR] G
 [COLOR=Navy]If[/COLOR] R = 2 [COLOR=Navy]Then[/COLOR] .Item(K).EntireRow.Interior.ColorIndex = 6: R = 0
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thanks Mick but it highlighted rows when they were in order like below

BM54544 2001Front Suspension
BM54544 2001Front Suspension
BM54544 2001Front Suspension
BM54544 2001Front Suspension
BM54544 2001Rear Suspension
BM54544 2001Rear Suspension
BM54544 2001Rear Suspension
BM54544 2001Rear Suspension

<tbody>
</tbody>

These are ok and should not be highlighted
 
Last edited:
Upvote 0
oldbrewer said:
out of interest what do you do with the out of sequence rows ?
Click to expand...

Because of about 100,000 rows maybe be only 6 will be out of sequence I will sort this manually as there are several dozen columns of criteria to be met which is why I cant do your suggestion.
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG04Jun07
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, G [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] .Item(K).Offset(, 32)
    [COLOR=Navy]If[/COLOR] Not R = 2 [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] G = "Rear Suspension" [COLOR=Navy]Then[/COLOR] R = 1
        [COLOR=Navy]If[/COLOR] R = 1 And G = "Front Suspension" [COLOR=Navy]Then[/COLOR] R = 2
    [COLOR=Navy]End[/COLOR] If
 [COLOR=Navy]Next[/COLOR] G
 [COLOR=Navy]If[/COLOR] R = 2 [COLOR=Navy]Then[/COLOR] .Item(K).EntireRow.Interior.ColorIndex = 6: R = 0
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

As you can see Mick below it is colouring the rows when they should all be left clear. The rows in yellow have Front Suspension first then rear as they should be. All these in the sample below should be clear because they are in the correct sequence.


Excel 2010
CAI
1CodeInfo_1
2AAROC18 3001Front Suspension
3AAROC18 3001Rear Suspension
4AAROC22D 3001Front Suspension
5AAROC22D 3001Rear Suspension
6AM40004D 1001Front Suspension
7AM40004D 1001Rear Suspension
8AM50005 1001Front Suspension
9AM50005 1001Rear Suspension
10AM50005 7001Front Suspension
11AM50005 7001Rear Suspension
12AM50005D 1001Front Suspension
13AM50005D 1001Rear Suspension
14AM50005D 5001Front Suspension
15AM50005D 5001Rear Suspension
16AM50005D 7001Front Suspension
17AM50005D 7001Rear Suspension
18AMCIT04D 1001Front Suspension
19AMCIT04D 1001Rear Suspension
20AMCOU04D 4001Front Suspension
21AMCOU04D 4001Rear Suspension
Sheet1
 
Upvote 0
Change the 1 in the code line to 2 lines as below.
Code: 
If R = 2 Then .Item(K).EntireRow.Interior.ColorIndex = 6
R = 0
 
Upvote 0
That seems to have done it, thanks Mick.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Question
Code To Highlight Rows When Out Of Sequence
Replies
4
Views
126
LuKate
L
Dazzawm
  • Question
Code To List Numbers Missing From Sequence
6 7 8
Replies
76
Views
7K
Dazzawm
Dazzawm
A
  • Question
Worksheet Display Not Updating Through Loop Process
Replies
3
Views
588
Ark68
A
S
  • Question
VBA - to paste between worksheets depending on cell values
Replies
3
Views
494
JLGWhiz
JLGWhiz
F
  • Question
VBA: Hide rows based on column A
Replies
8
Views
1K
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,203,742
Messages
6,057,112
Members
444,905
Latest member
Iamtryingman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top