Finding matching values in a differnt sheet and adding a fill color

[ROSCOPCO54]

So at work a guy goes through a spreadsheet and color codes different part numbers will the fill function depending on the status of the part then we get a new list of parts on another sheet and he has to manually go back and forth do highlight the cells on the new sheet. Is there a way when we get a new sheet that it can search for the part numbers and highlight them the same as the other sheet?

Thanks,

 

[Yongle]

Before answering your question ...

Your "guy" is probably slow in doing the original colouring of the cell and some VBA could speed up that process for him

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Part No[/td][td]Status[/td][td]No of
right clicks
in cell
in col A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FF0000]

1004​

[/td][td]Raw Steel[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#0000FF]

1009​

[/td][td]Raw Plastic[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#00FF00]

2007​

[/td][td]Work-In-Progress[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#FFFF00]

3001​

[/td][td]Finished[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

9001​

[/td][td]Unclassified[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#00FF00]

2009​

[/td][td]Work-In-Progress[/td][td]

3​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]



Here is something to test in a NEW workbook
Insert the code into the SHEET module as follows
- right click on sheet tab
- click on View Code
- paste the code below into the code window
- {ALT}{F11} to go back to the worksheet

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Const A = vbRed, B = vbBlue, C = vbGreen, D = vbYellow, E = xlNone
    Dim Clr As Variant, T As String
    With Target
        If .CountLarge > 1 Then Exit Sub
        If .Column = 1 Then
            Cancel = True
        
            Select Case .Interior.Color
                Case A:         Clr = B:        T = "Raw Plastic"
                Case B:         Clr = C:        T = "Work-In-Progress"
                Case C:         Clr = D:        T = "Finished"
                Case D:         Clr = E:        T = "Unclassified"
                Case Else:      Clr = A:        T = "Raw Steel"
            End Select
            .Interior.Color = Clr
            .Offset(, 1) = T
        End If
    End With
End Sub

Now right-click a few times in any cell in column A
- it loops through a sequence of 5 colours
- every right-click changes the colour in the cell and puts part "STATUS" in adjacent cell
- workbook should be saved as macro-enabled

If helpful, it can be modified to suit your needs

 

[Yongle]

In answer to your question in OP

Here is some VBA that can achieve what you want, but may need modifying to suit your requirements
- old list is in sheet List1 and new list is in sheet List2

Sub ColourParts()
    Dim newPart As Range, oldList As Range, newList As Range
'the 2 ranges
    Set oldList = Sheets("List1").Range("A2", Sheets("List1").Range("A" & Rows.Count).End(xlUp))
    Set newList = Sheets("List2").Range("A2", Sheets("List2").Range("A" & Rows.Count).End(xlUp))
'colour cell if part number matches
    For Each newPart In newList
        On Error Resume Next
        newPart.Interior.Color = oldList.Find(newPart, LookIn:=xlValues, LookAt:=xlWhole).Interior.Color
        On Error GoTo 0
    Next
End Sub

 

[ROSCOPCO54]

Thanks for the help. Could you give me some help on doing this last code. I'm a excel rookie.

 

[Yongle]

Did you try what I suggested in post #2 ?
- it will get you started with VBA
-

 

[ROSCOPCO54]

Ya, i tried that and got it working. Just a little confused on the code in the 3rd post.

 

[Yongle]

The code assumes that part numbers are in column A in both sheets starting at A2 and that both sheets are in the same workbook
- if your setup is different then let me know what is different and I will amend the code tomorrow

Otherwise
{ALT}{F11} takes you into VBA window
Insert Module with {ALT} I M
Paste the code into that module
Amend List1 and List2 to match the names of the 2 sheets in your workbook (List1 is the old parts list)
{ALT}{F11} to go back to Excel

{ALT}{F8} brings up list of macros to run

Remember to test on a COPY of your workbook!