I have an interesting situation and being new to VBA/Macros (only began using the last week) I'm at a lost and I've scoured the internet for the last week and can't find anything that can create an if and statement for what I need - because of the complexity of my macro it appears the simple delete duplicate is not working (it works perfectly if I isolate the code in a smaller workbook as I trouble shoot but the minute I add it to my complex macro it doesn't trigger) so I'm trying to come up with another solution
In column A I have used the macro to color code my duplicate values - I want to keep the first instance and delete the second one (but that too isn't working based off code I've found in various forums/youtube videos) so now I'm trying to just select the blank cell in corresponding column so I can insert text that will allow me to write code to delete rows based on cell value (I used something similar in my macro earlier on that works)
Here is the complexity my macro is working with three workbooks I'm working off of - Workbook1 is a daily assignment workbook that is created at start of the day for employees to update as they work their inventory this has a worksheet for each assigned employee (i.e tab 1 is Joe's tab, tab 2 is Sam's tab) - Workbook 2 - contains all inventory all ee's are on sheet1 and is updated throughout the day as new work comes into our queue - we then copy and paste new items to Workbook1(that has now been uploaded to sharepoint) - Workbook 3 is a report that contains inventory at the start of the day (it's where we pull the assignments)
I have workbook 1 and workbook 2 copying and pasting data and updating the workbooks correctly for the start of the day - my issue is with workbook 3 and workbook 2 - I can get the data from workbook 3 pasted into workbook 2 however due to the fact that inventory may be carried over - once I have the data pasted from workbook 3 into 2 I need to delete the duplicate in workbook 2 as it already been assigned to an ee and we don't want duplicates on the workbook - kind of like the below row 2 and row 5 are duplicates
Using code from earlier in my macro in which I go from workbook1 to workbook2 and update the value in column 12 I got the below to work correctly. So I thought being able to modify it would be easy but I'm in over my head and can't get this to work as it needs to look at two spots to update the value as I don't want to change the 1st occurrence value **I did update the names so that the Macro knows it's not duplicate code**
how can I get this to check the interior color for Cell A5 along with the blank in M5 (my range is dynamic as it could change daily) I tired to add AND after I've identified the cell color but I'm too green in VBA/Macro that I don't know what I'm doing to get it to work as after a week of videos and playing I'm no closer to solving this - any help or a better suggestion on how to get this to work would be greatly appreciated and thanks for taking the time to assist!
Worksheets("Sheet1").Activate
Dim rng As Range
Dim rngCell As Range
Dim allws As Worksheet
Set allws = Workbooks("Book2.xlsx").Worksheets("Sheet1")
res_count = allws.Cells(allws.Rows.Count, "A").End(xlUp).Row
Set rng = allws.Range("A2:V" & res_count)
For Each rngCell In rng
If Cells(rngCell.Row, rngCell.Column).DisplayFormat. _
Interior.Color = RGB(255, 199, 206) Then
rngCell.Offset(0, 12).Value = "DupRemove"
In column A I have used the macro to color code my duplicate values - I want to keep the first instance and delete the second one (but that too isn't working based off code I've found in various forums/youtube videos) so now I'm trying to just select the blank cell in corresponding column so I can insert text that will allow me to write code to delete rows based on cell value (I used something similar in my macro earlier on that works)
Here is the complexity my macro is working with three workbooks I'm working off of - Workbook1 is a daily assignment workbook that is created at start of the day for employees to update as they work their inventory this has a worksheet for each assigned employee (i.e tab 1 is Joe's tab, tab 2 is Sam's tab) - Workbook 2 - contains all inventory all ee's are on sheet1 and is updated throughout the day as new work comes into our queue - we then copy and paste new items to Workbook1(that has now been uploaded to sharepoint) - Workbook 3 is a report that contains inventory at the start of the day (it's where we pull the assignments)
I have workbook 1 and workbook 2 copying and pasting data and updating the workbooks correctly for the start of the day - my issue is with workbook 3 and workbook 2 - I can get the data from workbook 3 pasted into workbook 2 however due to the fact that inventory may be carried over - once I have the data pasted from workbook 3 into 2 I need to delete the duplicate in workbook 2 as it already been assigned to an ee and we don't want duplicates on the workbook - kind of like the below row 2 and row 5 are duplicates
| Invoice # | Queue | Date Created | Client # | Notes from client | Assigned to EE | Assigned to Date |
| 123456 | Priority 1 | 08/29/23 | 123456789 | please review x | EE1 | 08/29/23 |
| 234567 | Priority 1 | 08/29/23 | 234567890 | please review y | EE2 | 08/29/23 |
| 345678 | Priority 2 | 08/29/23 | 345678901 | please review z | EE1 | 08/29/23 |
| 123456 | Priority 1 | 08/29/23 | 123456789 | please review x |
Using code from earlier in my macro in which I go from workbook1 to workbook2 and update the value in column 12 I got the below to work correctly. So I thought being able to modify it would be easy but I'm in over my head and can't get this to work as it needs to look at two spots to update the value as I don't want to change the 1st occurrence value **I did update the names so that the Macro knows it's not duplicate code**
how can I get this to check the interior color for Cell A5 along with the blank in M5 (my range is dynamic as it could change daily) I tired to add AND after I've identified the cell color but I'm too green in VBA/Macro that I don't know what I'm doing to get it to work as after a week of videos and playing I'm no closer to solving this - any help or a better suggestion on how to get this to work would be greatly appreciated and thanks for taking the time to assist!
Worksheets("Sheet1").Activate
Dim rng As Range
Dim rngCell As Range
Dim allws As Worksheet
Set allws = Workbooks("Book2.xlsx").Worksheets("Sheet1")
res_count = allws.Cells(allws.Rows.Count, "A").End(xlUp).Row
Set rng = allws.Range("A2:V" & res_count)
For Each rngCell In rng
If Cells(rngCell.Row, rngCell.Column).DisplayFormat. _
Interior.Color = RGB(255, 199, 206) Then
rngCell.Offset(0, 12).Value = "DupRemove"
