Add user-based color functionality to macro that tracks changes

It looks like you have all the information that you need. I'd suggest that you set up separate sheet and list the user names and the colour code you want for each user in a table. Then use the user name to look up the table and find the colour. Plug the colour into your code.

Regards

Sorry, I think I was unclear in my explanation. Based on my understanding of your suggestion, that would help me color code the worksheet with the log of all the changes.

I was actually looking to have the cells on the original worksheet change colors based on the user that makes the changes. This would be separate from what happens on the log worksheet.

Did I understand your suggestion correctly?

By the way, I did not write the original macro. I actually found it on another thread, so I'm not too familiar with VBA. Any code that you could share would be much appreciated.

Thanks for your help.

Hi foxguy,

So the file you sent me works exactly the way I envisioned, so thank you very much for that.

I'm now trying to copy the macro into the file that I actually need it for and am running into some issues. Here is the code with adjustments in sheet #'s to match the file:

Please Login or Register  to view this content.

I thought I changed all the sheet references to match the worksheets, but I keep getting an error message saying "Run-time error '9': Subscript out of range" when I go to make a change on the worksheet. When I click debug, the following code is highlighted in yellow:

Please Login or Register  to view this content.

I thought I had changed the reference properly since the usernames are definitely listed in column A on sheet 4.

Any idea on why the error is coming up?

Thanks again for your continued help.

JM

foxguy,

So I think we're inches away from closing this one out. The color functionality now works and it's great. But now the "Before" column on the log, which showed the cell's previous value, is not populating. Any idea why that might be?

I have copied the code below for your reference:

Please Login or Register  to view this content.

Thank you for your continued help.

JM

Foxguy,

I tried pasting in the change that you recommended so that the code now reads like this:

Please Login or Register  to view this content.

It actually didn't work at first, but then I moved the line that said ".Cells(lRow, "H").Value = Target.Value" before ".Cells(lRow, "G").Value = .Cells(lRow, "H").Value" instead of after. It worked after that. I'm glad that it's working, but just want to make sure it's working for the right reasons. Was that just because the macro needed to see the one value first?

Anyhow, I think we're all set here. Thanks so much for all your help!

JM

Thanks for the explanation. I understand what you said and the log is definitely doing the wrong thing upon second glance. Unfortunately, putting in the suggested code doesn't seem to be working. When I make a change, column H definitely shows the new value that I put in, but column G remains empty.

Just to confirm, column G shouldn't necessarily have the previous H value, assuming previous means the previous row. There would be cases when changes are made to different cells in random sequence. In those cases, using the previous row would return the wrong "old" value for the edited cell. Does the code know to check the cell reference to make sure it's pulling the right "old" value for column G?

Any idea why this is happening?

I have attached a file with the same sheet set up and demonstrated the issue. You'll notice that column G is empty despite making edits to the same cell several times.

Thanks again for your continued help.

JM

Sorry, didn't work again

Let's try that one more time.

Foxguy,

Interesting development. I'm wondering though if there's anything from the old code that could be used.

Old code:

Please Login or Register  to view this content.

With that code, the before column is populated in the way I would want it to work. My understanding of code is pretty basic though. Can you see what in that code makes the before functionality work? Is there any way to integrate that part with the revised code that you put together?

If not, would you mind showing me how to use "Worksheet_SelectionChange(Target as Range)." Seeing the code or building on that sample file would be really helpful.

Thanks so much,

JM

Actually I think I figured it out.

From what I can tell, the original code I put in the last post was creating a mirror worksheet every time a change was made. Then each time a cell was changed the second time, the macro checked the mirror worksheet to find the old value and pasted it in the "Before" column. While this isn't the cleanest solution, it does serve my purposes well enough.

Here is the code that I put together using the two versions:

Please Login or Register  to view this content.

Please let me know if this solution is sustainable and stable.

Thanks again for your continued help.

JM

Thanks for the confirmation, Foxguy.

Agreed that some kind of FIND method would be cleaner, but I'm eager to have this completed and move on to the next thing.

Thanks so much for your help throughout this process.

Is there any particular way to mark this thread as solved aside from typing that?

SOLVED

JM