OzGrid

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:

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:

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.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery