PDA

View Full Version : Macros match / Lookup



KK1966
03-10-2009, 07:11 AM
Dear all

I wane a code likes Vb or Macros if it can match the value Form active Sheets1 Range “L3 to L” ( which that’s unknown since too many data to down )to work Sheets2 the Range from “B3 to B” if the value can matched than Sheet1 the targets Row from A to L highlighted yellow,


Does it workable? That I don’t know but wanna someone can help


Thanks :help

nst1107
03-10-2009, 08:53 AM
Sorry, bud. Need to work on your translation.

MaximS
03-10-2009, 01:57 PM
try that:


Option Explicit
Sub Checker()
Dim Wb As Workbook
Dim Sh, Sh1 As Worksheet
Dim LRow, LRow1, i As Long
Dim x As String
Set Wb = ThisWorkbook
'Replace 1 with Worksheet Name ("Name") if needed
Set Sh = Wb.Worksheets(1)
'Replace 2 with Worksheet Name ("Name") if needed
Set Sh1 = Wb.Worksheets(2)
'LRow will check for the last used row in Sheet 1
LRow = Sh.Range("A" & Rows.Count).End(xlUp).Row
'LRow1 will check for the last used row in Sheet 2
LRow1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row

'If you have a header change i = 1 to i = 2
For i = 1 To LRow
On Error Resume Next
x = WorksheetFunction.Match(Sh.Cells(i, "L").Value, _
'If you have a header change B1 to B2
Sh1.Range("B1:B" & LRow1), 0)
On Error GoTo 0
If x <> "" Then
Sh.Range("A" & i & ":L" & i).Interior.ColorIndex = 6
End If
x = ""
Next i

End Sub

KK1966
03-11-2009, 08:36 AM
Dear MaximS

Thanks The code and it work perfectly.:clap: :clap: