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.
Related:
Macro that changes a row color at the change of each job number.
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?
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.
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.
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.
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.
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.
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.
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?
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?
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.
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.
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.
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
