Highlight Entire Row based on Xlookup

[tedholly]

I have an xlookup that matches my column A Serial number to a master data sheet. The Xlookup tells me which row the value is located in. How can I highlight the entire row of the corresponding Serial Number in the Master Data tab?

 

[kweaver]

What about:

Sub TedHolly()
Dim r As Long, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
r = WorksheetFunction.Match(Cells(2, "K"), Sheets("Master").Range("A1:A" & lr), 0) ' change Cells(2, "K") to where the Serial # is located
Sheets("Master").Range("A" & r & ":H" & r).Interior.ColorIndex = 6 ' for example: yellow; change value for different color; change "H" for different end of range or set to extent of columns
End Sub

This doesn't provide for an error if the S# isn't found.

 

[tedholly]

What about:

Sub TedHolly()
Dim r As Long, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
r = WorksheetFunction.Match(Cells(2, "K"), Sheets("Master").Range("A1:A" & lr), 0) ' change Cells(2, "K") to where the Serial # is located
Sheets("Master").Range("A" & r & ":H" & r).Interior.ColorIndex = 6 ' for example: yellow; change value for different color; change "H" for different end of range or set to extent of columns
End Sub

This doesn't provide for an error if the S# isn't found.

I am new to VBA so please bear with me. I am getting an error with this code.

So on Sheet 1 column N is the serial numbers that I want to match with Sheet 4 Column A. Column B on sheet 4 States which line on Sheet 1 the serial number is located. I want to highlight the row numbers on sheet 1 that match to sheet 4. (Hope this makes sense)

How can I fix this error?

 

[Fluff]

You can also use conditional formatting for this.
Select the relevant cells on the master sheet (say A1:Z100) and use this formula in conditional formatting on that sheet

=MATCH(ROW(),Lookup!$B$2:$B$20,0)

 

[kweaver]

See if this is what you mean if, for some reason, you don't want to use CF.

Sub TedHolly()
Dim r As Long, lrm As Long, lr1 As Long, i As Long, c As Long, cl As Variant
lrm = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row ' number of rows in master
lr1 = Sheets("Sheet1").Cells(Rows.Count, "N").End(xlUp).Row ' number of rows w/S#
c = Sheets("Master").UsedRange.Columns.Count
For i = 1 To lr1
r = WorksheetFunction.Match(Cells(i, "N"), Sheets("Master").Range("A1:A" & lrm), 0)
cl = Cells(r, c).Address
Sheets("Master").Range("A" & r & ":" & cl).Interior.ColorIndex = 6 ' for example: yellow
Next i
End Sub

 

[tedholly]

You can also use conditional formatting for this.
Select the relevant cells on the master sheet (say A1:Z100) and use this formula in conditional formatting on that sheet

=MATCH(ROW(),Lookup!$B$2:$B$20,0)

This worked; thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.