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

ROSCOPCO54

New Member
Joined
Jul 1, 2019
Messages
3
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,
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
A
B
C
1
Part NoStatusNo of
right clicks
in cell
in col A
2
1004​
Raw Steel
1​
3
1009​
Raw Plastic
2​
4
2007​
Work-In-Progress
3​
5
3001​
Finished
4​
6
9001​
Unclassified
5​
7
2009​
Work-In-Progress
3​
Sheet: Sheet8



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

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0
Did you try what I suggested in post #2 ?
- it will get you started with VBA
-
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top