How to use VBA code to highligh rows

How to use VBA code to highlight rows

Requirement:

The user has a list of full names in column A with their numbers in column B, and the user has last names in column C with their numbers in column D.

The user has been trying to write a code where it would check if the last name in C is found anywhere in A, and if so, check if the number in column D matches column B, and if so, highlight the column that matches up from range A to B. So if the names match but the number is different or if the names dont match at all, then the fill color will be nothing.

This is what the user has so far:

Code:

Sub g()
Dim i As Range
Dim k As Range
Dim j As Integer
j = 1
Set k = Range("a1:b10000")

For Each i In k
If IsError(Application.Match(i.Value, ActiveSheet.Columns("C"), 0)) Then
If IsError(Application.Match(i.Value, ActiveSheet.Columns("D"), 0)) Then
i.Interior.ColorIndex = 0
Else
i.Interior.ColorIndex = 6
End If
End If

Next i
End Sub

The excel file is also attached - please refer to the following link:

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1203263-highlighting-rows

Solution:

Code:

Sub HighlightRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lName As Range
    Dim foundName As Range
    For Each lName In Range("C1:C" & LastRow)
        Set foundName = Range("A:A").Find(lName, LookIn:=xlValues, lookat:=xlPart)
        If Not foundName Is Nothing Then
            If lName.Offset(0, 1) = foundName.Offset(0, 1) Then
                foundName.Resize(, 2).Interior.ColorIndex = 6
            End If
        End If
    Next lName
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.