Code to check if a work centre on sheet 1 appears on sheet 2 [Closed]

Report
Posts
1
Registration date
Monday February 20, 2017
Status
Member
Last seen
February 20, 2017
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hi,
Please help me write a code that will check if a work centre on sheet 1 appears on sheet 2. If it appears in sheet 2 the work centres in sheet 1 must be highlighted in light grey. I have no VBA Experience or any significant programming experience so any assistance will be appreciated.

1 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi MsVictorry,

Assuming column A is used on both sheets for the work centers and at least sheet1 has an header. Also sheet 1 is called "Sheet1" and sheet 2 is called "Sheet2".

Give this code a try:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        cell.Interior.TintAndShade = -0.149998474074526
    End If
Next cell

End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!