Macro that changes a row color at the change of each job number. Solved/Closed

Question

Hello, 

I have a spreadsheet that is say 5000 rows long and 15 columns wide and contains production job numbers in Column A.  The details associated with that job, (ie customer, salesman, etc,) appear in columns B through P.  The first job number appears in row A2.  I have sorted Column A in Ascending order.  I want a macro that will color the first row as Yellow.  Then, if the text or value in A3 is the same as A2 then color that row Yellow as well.  If Not, then change the color of row 3 to be Orange. The purpose is to color all consecutive rows that share the same job number as one color, and if the job number changes, then the color of the rows should change as well.  The macro needs to be written in such a way that it changes the row's color as the job numbers change.  This way when I am reading down through the spreadsheet I can easily see how many rows are tied to one job vs another.  A job may only consist of one row.  However, some jobs could consist of 10 rows.  If 10 consecutive rows shared the same job number then all 10 rows should be the same color. The colors should be set to toggle between yellow and orange or pink and green as the jobs change.  Any help you can give would be greatly appreciated.

vcoolio · Answer

Hello Rick,

There are a few ways that this can be done but, firstly, how many Job Numbers do you use? Are there just, say, five job numbers that are repetitive and constantly used (e.g. Job No.1, Job No.2, Job No3, Job No.4, Job No.5) or are there tens (or hundreds) of job numbers?

Cheerio,
vcoolio.

Rick · Answer

Thank you for your quick reply.  An example of a job number would be 100150D, or 101510S, or 101550K or 35682T.  So you can see that we are using both 5 digit and 6 digit numbers with an Alpha Identifier that breaks out our different Product Categories.  To me, this is a job number, but to a computer programmer this may constitute a text field perhaps since it contains both a number and an alpha character.  In any event, I have a report that is roughly 5000 rows long and let's say 15 columns wide.  Column A lists the job numbers. Columns B:H are details concerning that job (ie CustomerID, Salesman, SalesCode, WorkCenter, etc).  So the whole row needs to be highlighted because it relates to that one job number.  Now, the job number (100150D as mentioned above) may be listed on this report multiple times.  That's why I have sorted the report in job number order.  This groups and lists each job together in ascending order.  So now I may have job 100150D on row A2.  Let's say that it is the only job number like that in this spreadsheet.  But following that job number in row A3 is job 101510S.  And lets say that that job number is also found in rows A4, and A5.  What I am wanting is a Macro that I can run that will assign a color to row A2 (let's say yellow), and then as the Macro runs, it then looks at A3 and compares the value or text in A3 with A2.  If they are the same, then the row A3 would also be  colored in Yellow.  But because they are NOT the same, per our example, then I want the Macro to color the row as Orange.  At this moment A2 is Yellow and A3 is Orange. Then the macro continues forward and compares A4 to A3.  Those values are = so the Macro then colors row A4 as Orange as well.  Again, the macro continues and compares A5 to A4 and the values are the same so again, it colors the row A5 as Orange as well.  But when it compares A6 to A5, it finds that A6 is a different job number than what was in A5.  Therefore, it colors A6 as yellow, and so forth down the list of jobs until it gets down to the end of the spreadsheet.  What I should have once the Macro completes is my spreadsheet highlighted in yellow and orange rows and at each change in job, we find that the color changes.  This easily tells me which jobs have 1 row, which have 3 rows, and which have maybe 15 rows.  But the entire Spreadsheet may contain 5000 or more rows.  Obviously it needs to stop coloring rows when it reaches the last row with a job number in it.  I hope this helps.

vcoolio · Answer

Hello Rick,

After reading your second post and better understanding what you would like to do, it became obvious that, for such a large data set, using conditional formatting (either the in-built Excel function or VBA), your workbook would become bogged down in a quagmire of conditional formatting making it cumbersome and slow.

I would suggest that using the Advanced Filter would be a far better option and would be extremely fast in gathering data as required. Are you open to this suggestion?

Cheerio,
vcoolio.

Rick · Answer

Thank you for your continued interest, but that does not appear to be a good fit for me on this project.  I have all the filtering mechanisms that I need on the spreadsheet.  I just need a macro that I can run that blows through the whole spreadsheet (5000 rows or more) in just a few seconds that changes the row color at each change in job number. Something like a CNTL ALT Z and bang it's done.

vcoolio · Answer

Hello Rick,

Just getting back to the Job Numbers or Job IDs if you prefer.
Are they numbers from a list which are constantly re-used? If so, how many numbers are there that you use per product category?

Cheerio,
vcoolio.

Rick · Answer

No, the numbers are not reused.  In my earlier example where a job has 10 rows.  Each row is tied to a different Work Center Operation.  Hence some jobs may only take one Work Center to complete.  Another job might take 10 different Work Centers to complete.  Once the job is completed (hence, is closed and invoiced) that job automatically falls off the spreadsheet and will never appear on the spreadsheet again.  The Numbers of jobs per product category is not of any concern here.  I researched some VBA code last night and I am going to try to back into a solution.  I found two different videos that give me an idea of how to change the color of the row at each job interval, I just have to piece the two videos/statements together some way and I think it will work.
Thank you for your continued interest.

vcoolio · Answer

Hello Rick,

I've attached a sample workbook with this code assigned to the blue button on Sheet1:-

Option Explicit
Sub Test()

Dim ar As Variant, i As Integer
ar = [{"J1","J2","J3";6,4,8}]

Application.ScreenUpdating = False

For i = 1 To UBound(ar, 2)
    
                With Sheet1.Range("A1", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(1, i)
                    .Columns("A:P").Offset(1).Resize(.Rows.Count - 1).Interior.ColorIndex = ar(2, i)
                    .AutoFilter
                End With
    Next i
    
Application.ScreenUpdating = True

End Sub

The code operates on about 10,500 rows of data in the sample and executes instantly.
I believe the end result is what you had intended but this is just an example to show you how quickly a non looping type code will work (I am trying to avoid looping type codes). It is based on job numbers but, obviously, won't work in your workbook as the job numbers are not a constant. This is why I have asked questions about the job numbers. I'm trying to identify a key in your data set. Here are a couple of more questions:
- Does the letter, or alpha identifier, at the end of each five or six digits in the job number refer to the product category or type?
- If so, are these letters constant?

We would be interested to see what you come up with as well.

Here is the link to the sample:-

https://wetransfer.com/downloads/aeb3243e5de9ed3c8b001440f4d2043e20210212105151/9fed92

Cheerio,
vcoolio.

Rick · Answer

Thank you.  I will have to try this.  I spent 3 or 4 hours last night watching various videos and capturing bits and pieces that I thought might work for my intended purpose.  Most videos were in fact centered around looping so it will be interesting to see how your example works.  I'm anxious to try your sample.  But it may be next week before I get to it.  I am not sure what you mean by the job numbers NOT being constant.  I may need you to clarify that statement a little bit for me.  Perhaps you mean that some are 5 characters in length and some are 6 characters in length.  Some may begin with a 1(ie 100000T and others may begin with a 3 (ie 39657K.  Obviously the 5 digit job number is an older job and the higher number job is the most recent jobs.  If that is what you are referring to then that is correct.  The Alpha identifer does in fact identify the product category.  D is for Die, S is for Stamping, T is for Tooling, etc.  Each job should have an Alpha Identifier as the last character in the job.  The job numbers are assigned sequentially by our accounting system.  And then each person who creates an order identifies the order by product type by adding the D or S or T to the end of the job number as appropriate.  But in my mind, that shouldn't matter because all I am wanting it to do is to test A3 against A2 and assign one color if it's true and another color if it's false.  If they are = then highlight row A3:H3 Yellow.  If A4 is equal to A3, then highlight row A4:H4 Yellow as well.  Continue testing that column down the spreadsheet until it comes to a job number in column A that is not equal to the job number in the row above it.  At that point let's say A10 is NOT = to A9, then highlight A10:H10 in orange.  So that once the macro runs, I end up with a different color at each change in job number.  I am not sure what KEY is needed or sought out by your questions.  I do invite the questions.  You're obviously much more knowledgeable in this area than I am.  I will have to try this to see if we are on the right track or not.  Thank you so much for your time and interest in my task.  I greatly appreciate it.

vcoolio · Answer

Hello Rick,

You probably think that I'm obsessed with, in relation to your query, job numbers! Well, you're right!
How many alpha identifiers are there, or are used, in the product category?
We'll wait to hear from you next week.

Cheerio,
vcoolio.

Rick · Answer

D, P, M, N, B, S, SO, T, K, V, IN, ME, TR, M1T, M1K, are just a few examples.  Most jobs have only one alpha character, some as you can see will have more than one Alpha Character.  That's why A3 just needs to compare to A2 to see if those cell values are equal.  If Equal then Yellow, if Not Equal, then Orange.

It's pretty simple.  

I did not get to review your Macro from the other day.  I will hope to get to that this week.  Thank you for all your interest.

R

vcoolio · Answer

Hello Rick,

Based on your explanation in post # 12, download the sample in my post #7 and assign the following code to a button or shortcut key:-

Option Compare Text

Sub TestAgain()
       
        Dim c As Range, lr As Long
        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
                
Application.ScreenUpdating = False

Sheet1.Range("A2:P2").Interior.ColorIndex = 6
        
        For Each c In Sheet1.Range("A3:A" & lr)
                Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = xlNone
                If c.Value = c.Offset(-1).Value Then
                Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = 6
                Else
                Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = 45
                End If
        Next c
        
Application.ScreenUpdating = True

End Sub

It is a loop type code but, on the 10K plus rows in the sample, it should execute in 1-3 seconds.
It will assign a yellow colour to Row2 (A2:P2) first.

I hope that this helps.

Cheerio,
vcoolio.

Rick · Answer

Thank you.  I am not very good at reading code, but I can say that this  is very close to what I had intended to try myself.  I just have not had a chance to work much on it in the last week.  I will try this and get back with you.  But it may take me a few days to do that.  I'm spread too thin right now and I have to put this aside for the moment until I get other things more pressing accomplished.

I will get back with you though.

Thank you.

Rick

TrowaD · Answer

Hi Guys,

Vcoolio asked me to have a look at this thread to see if I can assist.

Vcoolio's code from post 13 is close, but it doesn't change the colors right (when the value in column A is the same, the color will always be yellow). I made a change to his code, so have a look:
Sub TestAgain()
Dim c As Range, lr As Long, mColorIndex As Integer

lr = Range("A" & Rows.Count).End(xlUp).Row
                
Application.ScreenUpdating = False
        
mColorIndex = 6
Range("A2:P2").Interior.ColorIndex = mColorIndex

For Each c In Range("A3:A" & lr)
    Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = xlNone
    If c.Value <> c.Offset(-1).Value Then
        If mColorIndex = 6 Then
            mColorIndex = 45
        Else
            mColorIndex = 6
        End If
    End If
    
    Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = mColorIndex
    
Next c

Application.ScreenUpdating = True

End Sub

Best regards,
Trowa

Rick1517 · Answer

Thank you for all your hard work and your time invested in this little project of mine.  I have finally gotten around to test the code that you gave to me.  It is very very very close to what I had in mind.  At each change in Job number this code is highlighting the first row of the next job with a different color.  That is exactly what I wanted.  

However, each job could have 8 or 10 or maybe 15 rows.  In our example we wanted the first row of Job 1 to be yellow, if row two was also Job 1 we wanted that row to be yellow also, and so forth.  Once it comes to a row with a different Job number, we wanted the row to change to Orange, and then that next row would compare it's job number to the job number above it.  If it were the same number, then the next row should also be orange, and so forth until the program reached a different job number and then it would change to yellow and stay yellow until it reached another job and then go back to orange.

Top_Lvl_Job
100768D
100768D
100768D
100768D
100768D
100768D
100768D
100768D
100768D
100768D
100768D
100768D
101317D
101317D
101317D
101317D
101317D
101317D


So I might have a job that has 12 rows.  Each of those rows would be Yellow.  The next job would come up and the row color would change to Orange, and that job might have say 6 rows.  Each of those 6 consecutive rows would then be Orange as well.  Each block of colors (Yellow or Orange) encompasses the total number of rows of a particular job.  If I see 12 Yellow rows, then I know that that job has 12 work centers tied to it.  If the next job has 6 Orange rows, then I know that it has 6 separate WorkCenters tied to it.  Visually the different colors are intended to express the number of WorkCenters for Each Job and "visually" separate the jobs so that I don't have all the jobs running together and I can't tell where one starts and the other ends.

So is there one simple line of code that we could add that would give me that affect?

Please advise.

Rick1517 · Answer

I copied this code over to a different spreadsheet of different data.  The application of it should be about the same.  What I got was interesting.  Every time the job number changed in the list the row would be highlighted in Orange.  The rest of the rows would appear as yellow.  I thought this worked pretty well until I noticed that Job 100012P should have been highlighted in Orange since it was different than the row (Job) above it.  But instead it was hightlighted as Yellow.

I am not sure why the macro would have skipped that particular job, or why it would not have colored it Orange as it should have been with any other job number change.  I checked to make sure there was no Conditional Formating set for this row.

What else could have caused Job 100012P to NOT follow it's ordained instruction to highlight as Orange at the change of a Job #?

Please advise.

Rick


Root Job
100002B
100002B
100002B
100002B
100002B
100003D
100003D
100003D
100003D
100003D
100004D
100004D
100005D
100005D
100007D
100008S
100010S
100011P
100011P
100011P
100011P
100011P
100011P
100011P
100011P
100011P
100012P
100013K
100014D
100015P
100016D

vcoolio · Answer

Hello Rick, I think the following should do the task for you:- Option Explicit Sub TestAgain() Dim c As Range, lr As Long lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Sheet1.Range("A2:P2").Interior.ColorIndex = 6 For Each c In Sheet1.Range("A3:A" & lr) Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = xlNone If c.Value = c.Offset(-1).Value Then Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = 6 Else Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = 45 End If Next c TestAgain2 Application.ScreenUpdating = True End Sub Sub TestAgain2() Dim c As Range, lr As Long lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For Each c In Sheet1.Range("A4:A" & lr) If c.Value = c.Offset(-1).Value And c.Interior.ColorIndex <> c.Offset(-1).Interior.ColorIndex Then Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = c.Offset(-1).Interior.ColorIndex ElseIf c.Value <> c.Offset(-1).Value And c.Interior.ColorIndex = c.Offset(-1).Interior.ColorIndex Then Range(Cells(c.Row, "A"), Cells(c.Row, "P")).Interior.ColorIndex = 6 End If Next c Application.ScreenUpdating = True End Sub I've attached a mock-up sample for you to show you how the above code works. It's actually two codes; the TestAgain code calls theTestAgain2 code. I've done it this way as your data set may become quite large and breaking loop-type codes into separate processes can help to speed them up. I've tested this on approx. 20K rows and it took about one second to execute. The sample has about sixty rows so it should work very quickly. Click on the "Update Status" button to see how it works. Test the code in a copy of your actual workbook prior to implementing it. Here is the link to the sample:- https://wetransfer.com/downloads/f445164afc843761b79801ea557caa5320210320063516/e16728 I hope that this helps. Cheerio, vcoolio.

vcoolio · Answer

Hello Rick,

What else could have caused Job 100012P to NOT follow it's ordained instruction to highlight as Orange at the change of a Job #? 

In relation to your above query in post #18, I don't see any problem. I transposed your job numbers into the sample I supplied in post #19 into Column A and both the codes supplied in posts #19 and 15 (TrowaD's code) colour code all job numbers correctly. 

Cheerio,
vcoolio.

Macro that changes a row color at the change of each job number.

17 responses

Similar discussions

Subscribe To Our Newsletter!