key sheet based macro

[Tonyk1051]

Hi,

Everyday i have to review changes made by several users and it takes around an hour to do.... in the keysheet attached is a list of categories my dept uses and the word "yes" means he/she is authorized to use that category. to the right is the name of every user and which dept they belong to. Example:some users belong to "warehouse" you'll see that the warehouse users are only allowed to use one or two categories anything else i will mark no in the actual spread sheet "user activity"

In the user activity spreadsheet, tab "category change detail"

the column that says "authorized" i put yes if the categories in column F follow the keysheet, if a cell in column F is blank then i leave it blank, if its a category that not marked yes in the keysheet then i put the word no

when all done i sort the entire spread sheet by column D and look for any duplicates. Any ID which is duplicate I color (light blue) the line that has an earlier date
so ID:7716899 appears twice, one has 5:30pm the other has 6:00pm , the one that is 5:30pm is colored . then i call it day.

hopefully someone can help me

key sheet.xlsx keysheet

RTV User Activity 2023-01-06.xlsm spreadsheet to apply macro

 

[Tonyk1051]

The way breynolds0431 has written the code adding more user names in the user to group mapping table will work fine.
Adding more user "columns" however will move columns M:N which are hard coded in the code.

Change the line of code below and that should address the additional column issue.

' Replace the next line
'Dim ulRng As Range: Set ulRng = xWB.Sheets(1).Columns("M:N")
' With this line
Dim ulRng As Range: Set ulRng = xWS.Cells(1, Columns.Count).End(xlToLeft).CurrentRegion

Breynolds and Alex, thank you guys for the help. much appreciated

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
Can I suggest that you mark breynolds post #2 as the solution, the other posts are only minor tweaks based on some slight changes to the requirements, and won't help others as much the post #2 will.