I THINK a worksheet change event is in order, but there might be a better way

[zookeepertx]

Hi there!

A co-worker has a workbook to keep track of employees' HazMat training classes. When she adds a new employee to the list, their data gets highlighted green, lavendar or blue, depending on what region they're in.

The master sheet is called HazMat Training and there are 3 more tabs, one for each region. For sheet 2, which is named Northeast, the tab is colored green, for sheet 3 the tab is colored lavendar, for sheet 4 the tab is colored blue.

When she creates a new entry on the HazMat Training sheet, what she would like to happen is, whatever color she highlights their row, for that row of data to also appear on the correspondingly colored tab. (All columns are the same on all 4 sheets, so just being copied and pasted would work fine).

I've searched all over for worksheet change coding and what I'm finding all seems to be geared to calculating something or find information or something. I haven't seen anything to copy/paste an entire row to another sheet, and certainly not to a specific sheet depending on cell color/tab color.

Is this even possible? If so, I would be so happy to learn how!

Thanks!

Jenny

 

[Eric W]

Hi Jenny!

It is possible to write a macro that will copy one row to another sheet. The problem is figuring out how to trigger it. As you've found, the most common event triggers are when a cell is changed, when a selection changes, when the sheet recalculates, or possibly when someone double-clicks on a cell, or right clicks on a cell. None of those seems ideal for your situation (although if my next idea doesn't work for you, then we could revisit one of them). Changing a background color does NOT trigger an event.

Another idea is to use the Sheet_Deactivate trigger. So your co-worker can make all the changes she wants on the HazMat sheet, including the highlighting, and when she's done, she can click on another sheet tab. This triggers the Deactivate event. Then a macro can read down the column, looking for names with highlighting. If it finds one, it checks the matching sheet to see if it's already there. If not, it adds it to the end of the column. So the names aren't added as she types them, or when she highlights them, but they will be in place before she can switch sheets.

If you want to try that, open a copy of her workbook. Press Alt-F11 to open the VBA editor. On the left side navigation pane, double-click on the "Sheet1 (HazMat Training)" object. Paste the following code into the window that opens:

Private Sub Worksheet_Deactivate()
Dim Regions As Variant, i As Long, r As Long, r2 As Long, FindCell As Range
Dim ShtColor(0 To 10) As Long

    Regions = Array("Northeast", "South", "West")
    For i = 0 To UBound(Regions)
        ShtColor(i) = Sheets(Regions(i)).Tab.Color
    Next i
    
    For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        For i = 0 To UBound(Regions)
            If Cells(r, "A").Interior.Color = ShtColor(i) Then
                Set FindCell = Sheets(Regions(i)).Columns("A:A").Find(Cells(r, "A"))
                If FindCell Is Nothing Then
                    r2 = Sheets(Regions(i)).Cells(Rows.Count, "A").End(xlUp).Row
                    Sheets(Regions(i)).Range("A1:E1").Offset(r2).Value = Range("A1:E1").Offset(r - 1).Value
                End If
                Exit For
            End If
        Next i
    Next r
    
End Sub

Change the values in red to match her workbook. This macro looks for the color and name in column A of the HazMat Training sheet, and looks for a matching name in column A of the region sheets. It then copies columns A:E of the current row to the last row. (Leave the 1s in the range, just change the columns.) Close the VBA editor. Note that when checking the cell color, it must match the color of the tab. If you pick a slightly different shade, it won't work.

Try it out! Let me know how it works.

 

[zookeepertx]

Hi Eric!

That's awesome! I just had to tweak a few column references and everything works just right! There's just ONE more little thing: is it possible to have the colors of the cells appear on each of the region sheets when they're brought over from the HazMat sheet?

Re: cell colors. I already told her she'd better make sure those are the specific colors she wants, because they MUST stay the same forever, LOL!

This looks great so far! I was having a really awful day until I saw your reply, but now I'm better. (They "upgraded" Microsoft Office overnight: it loaded onto some people's computers, but on others it just deleted the old versions and didn't give us the new ones! Then, when they came around and fixed that, we had to try to figure out how to USE the new Excel, Word and PowerPoint. THEN a program that a lot of people used developed an emotional problem and we can't get logged into it..... It's been (NOT) fun.)

Thanks so much,

Oh - the workbook will have to be saved as macro-enabled, right?

Jenny

 

[Eric W]

Sure! Just change this line:

Sheets(Regions(i)).Range("A1:E1").Offset(r2).Value = Range("A1:E1").Offset(r - 1).Value

to

Range("A1:E1").Offset(r - 1).Copy Sheets(Regions(i)).Range("A1:E1").Offset(r2)

and it will copy over any formatting and highlighting that was placed on the HazMat sheet. Or you could just add this line after the original line:

Sheets(Regions(i)).Range("A1:E1").Offset(r2).Interior.Color = ShtColor(i)

and it will just add the color to the range. And yes, you do need to save this as a macro-enabled workbook.


I'm sorry you had such a bad day! But don't worry, a brighter day will come!

Glad I could help!

 

[zookeepertx]

YIPPEEE!! That's perfect! As far as I can tell, everything works just as needed. I was (as is normal for me) WAY overthinking it; never would have come up with this.

You made my day; thank you! Hope you're having a good day

Jenny

 

[Eric W]

Woo hoo! Glad it works for you! Let me know if you need anything changed. I can imagine some possible changes, like what do you want to do if you delete a name?

In any case, have a good evening!

 

[zookeepertx]

Hi Eric,

Well, I'm sure people ask you for solutions, then, when you have an answer, they say, "Oh, well I thought it would come out like THIS." Well, here we are! (But this one's not my fault)

My co-worker has given me more info as to what she needs her result to be and it would be a lot easier to show you than to try to explain it without visual aids.

I've looked at the thread about attaching/posting screenshots and it appears (I could be wrong) that you have to download something to use any of them. I don't think I'd be allowed to download anything on my computer here at work - I actually can't ask our IT guy because he's out putting out fires from a huge phishing attack we had this morning. (Which makes me think it's even MORE unlikely I could download something right now)

Is there anyplace, like maybe a "hosting" website, where I could upload a small example workbook so you could see it? The only other alternative I can think of would be to post each sheet in here as a table, which looks like it would be a very large/long post. But I can do that if needed.

I'll make a post below to TRY to get across the necessary information, just in case I'm able to make sense, LOL!

Thank you!

Jenny

 

[zookeepertx]

Hi again, Eric,

Okay, let me give this a try with tables

First the HazMat Training sheet. Newly added rows are in bold; the others already existed and are on the "regional" sheet as well.

Supv./Ops.Mgr.	Associate	Training Begin Date	Trainer	Store #		Completion		Needs Training by:
						DATE	TIME	(2 YEAR)	Expired
Amy Brown	Tom Smith	1/20/2019		3456		2/17/2019		2/17/2021	Blue	1/18/2021
David Jones	Bob Marshall	4/27/2018		ABCD		4/27/2018		4/27/2020	Green	3/28/2020
Beth Green	Karen Thomas	10/12/2018		1025		1/8/2019		1/8/2021	Green	12/9/2020
Beth Green	Mike James	1/9/2019		1025		1/9/2019		1/9/2021	Green	12/10/2020
Mary White	Patty Shore	2/17/2019		246					Lavender
Jim Daniels	Jeff Williams	11/2/2018		1234		1/30/2019			Green
David Jones	Cliff Stone	4/27/2018		ABCD		4/27/2018		4/27/2020	Green	3/28/2020
David Jones	Sharon Poole	5/16/2018		ABCD		5/16/2018		5/16/2020	Green	4/16/2020
Mary White	Grace Jackson	1/20/2019		246		2/17/2019		2/17/2021	Lavender	1/18/2021
Mary White	Bob Lyons	2/17/2019		246					Lavender
Jim Daniels	Bill Short	11/2/2018		1234		1/30/2019			Green
Amy Brown	Jean Green	1/20/2019		3456		2/17/2019		2/17/2021	Blue	1/18/2021

<tbody>
</tbody>

(The table cells won't let me color them, so I typed in their color)

And the Northeast sheet, which is for the green highlighted data. First, here's how it looks before new rows are added.

Supv./Ops.Mgr.	Associate	Training Begin Date	Trainer	Store #		Completion		Needs Training by:
David Jones	Bob Marshall	4/27/2018		ABCD		4/27/2018		4/27/2020		3/28/2020
Beth Green	Karen Thomas	10/12/2018		1025		1/8/2019		1/8/2021		12/9/2020
Beth Green	Mike James	1/9/2019		1025		1/9/2019		1/9/2021		12/10/2020
Jim Daniels	Jeff Williams	11/2/2018		1234		1/30/2019

<tbody>
</tbody>

Then, with the new rows included.

Supv./Ops.Mgr.	Associate	Training Begin Date	Trainer	Store #		Completion		Needs Training by:
David Jones	Bob Marshall	4/27/2018		ABCD		4/27/2018		4/27/2020		3/28/2020
David Jones	Cliff Stone	4/27/2018		ABCD		4/27/2018		4/27/2020		3/28/2020
David Jones	Sharon Poole	5/16/2018		ABCD		5/16/2018		5/16/2020		4/16/2020
Beth Green	Karen Thomas	10/12/2018		1025		1/8/2019		1/8/2021		12/9/2020
Beth Green	Mike James	1/9/2019		1025		1/9/2019		1/9/2021		12/10/2020
Jim Daniels	Jeff Williams	11/2/2018		1234		1/30/2019
Jim Daniels	Bill Short	11/2/2018		1234		1/30/2019

<tbody>
</tbody>

So, basically, the new rows (the bold ones) need to go to their correct regional sheet (in this case Northeast which is green). THEN, if possible, they need to be inserted below that last existing row of data for the matching Store #.

I'm terrible at explaining things, so it this doesn't make sense, let me know & I'll try again.

If this can't be done easily but might be accomplished with a macro instead, just tell me, because we can go that way if necessary.

Thanks for your patience!

Jenny

 

[Eric W]

This will still require a macro. Depending on what you (or your co-worker) want, it might not be too much of a change. Option 1 would be to use the existing macro, but to look at column B (associate) for the new name, then once all the names have been moved to the right sheets, sort the sheets by columns A and B. Downside is that the names could change location, but the upside is that they will be alphabetical, so easier to find. Option 2 would be to find the right sheet, find the last row with the right supervisor (or last row if that supervisor is not found), and insert the new row there. Neither one is particularly hard to do. Let me know how you want it.

 

[zookeepertx]

Hi Eric,

I checked with her and she wants each Regional page to be in order by Store # (column E) first, then by associate. But each store needs to have 1 uncolored, blank row separating it from the other stores. That part doesn't show up very well on the tables in my last post; sorry about that.

Oh, also, a couple of the stores are identified by letters instead of numbers. Those can be at the top or bottom, it doesn't matter, but they do need to be sorted. They may be 2 letters, 3 letters or 4 letters. (ex. AB, CDE, FGHI)

Thank you!

Jenny