Help with colour in code

[Dazzawm]

I have the code below which looks on sheet 1 and puts on sheet 2 the number in the adjacent column. What do I add to the code that once the data is copied into the cell in sheet 2 it colours the cell yellow so I can idenitfy it? Thanks

Sub DazzawmsCode()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet2")
LR = .Range("AB" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("AB" & i)
Set Found = Sheets("Sheet1").UsedRange.Find(what:=.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then .Offset(, 1).Value = Found.Offset(, 1).Value
End With
Next i
End With
End Sub

 

[VoG]

Try

If Not Found Is Nothing Then
    .Offset(, 1).Value = Found.Offset(, 1).Value
    .Offset(, 1).Interior.ColorIndex = 6
End If

 

[Dazzawm]

Try

If Not Found Is Nothing Then
    .Offset(, 1).Value = Found.Offset(, 1).Value
    .Offset(, 1).Interior.ColorIndex = 6
End If

That seemed to turn the entire column yellow!?

 

[VoG]

It shouldn't do - it should only copy values and colour the cell when a match is found.

 

[Dazzawm]

It shouldn't do - it should only copy values and colour the cell when a match is found.

Well it did!! I have posted the code as it is now so you can see if there isnt anything untoward.

Sub DazzawmsCode()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet2")
LR = .Range("AB" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("AB" & i)
Set Found = Sheets("Sheet1").UsedRange.Find(what:=.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then .Offset(, 1).Value = Found.Offset(, 1).Value
        .Offset(, 1).Interior.ColorIndex = 6                           
End With
Next i
End With
End Sub

 

[VoG]

It should be (as per my original reply)

Sub DazzawmsCode()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet2")
    LR = .Range("AB" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("AB" & i)
            Set Found = Sheets("Sheet1").UsedRange.Find(what:=.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not Found Is Nothing Then
                .Offset(, 1).Value = Found.Offset(, 1).Value
                .Offset(, 1).Interior.ColorIndex = 6
            End If
    End With
    Next i
End With
End Sub

 

[Dazzawm]

That doesnt work at all comes up with an error End If without block if.

 

[VoG]

That compiles for me; tested and working.

 

[Dazzawm]

It works now, the only difference I can see between your code and mine is I had the 2 offsets on different lines. I didnt think that would make a difference. Also why is there an End If in the code now after those 2 offsets?

 

[VoG]

There is an End If because it is now a block If instead of your original in-line If.