Excel VBA – Find String in Range – Return Row – Change Background Color – Remove Duplicates

Excel VBA Find String – In Range of Cells

Get code to perform search operation for a string in Excel worksheet cell range.

Commands Used: Range.Find & Range.FinNext

Pass the value to be searched & range object as parameter to this function. It will first use .Find to get first occurrence of the string. Then it will search all further cells using .FindNext & change the background color of all matched cells.

Excel VBA – Find All Strings in Range – Highlight Cell Color

This function will search for the value in range. If found, the background color of the cell is changed to yellow.

This way, it will be easy to identify whether the value that we are searching is present in the range or not.

'--------------------------------------------------------------------------------
'Code by author@officetricks.com - Find All String in range using Excel VBA Code
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Sub Find_All_String_In_Range(FindString As String, iRng As Range)
    Dim Rng As Range
    'Set irng = ThisWorkbook.Sheets(shName).Cells

    If Trim(FindString) <> "" Then
        With iRng
            Set Rng = .Find(What:=FindString, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                If Not rng Is Nothing Then
                    With rng.Interior
                        .Color = vbYellow
                    End With

                    firstAddr = rng.Address
                    'Find for same Text in Rest of Sheet. FindNext
                    Do
                        Set rng = .FindNext(rng)
                        If Not rng Is Nothing Then
                            With rng.Interior
	                        .Color = vbYellow
                            End With
                        Else
                            Exit Do
                        End If
                    Loop While firstAddr <> rng.Address
                Else
                    Set rng = Nothing
                End If
        End With
    End If

End Sub

If you just need to find only the first occurrence, then use only the range.find method.

Excel VBA – Find First String in Range – Return Row

This function will return the address of the cell that has the search string.

Using the address or range object, you can get the row number as Range.Row

Function Find_First_In_Range(FindString As String, iRng As Range) As Range
    Dim Rng As Range
    'Set irng = ThisWorkbook.Sheets(shName).Cells
    If Trim(FindString) <> "" Then
        With iRng
            Set Rng = .Find(What:=FindString, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
    Set Find_First_In_Range = Rng
End Function

Other Common uses of this Range.Find Function are:

1. Finding the row number of a string in a range of cells.
2. Change background color of cell based on the value in cells.
3. Finding number of occurrences of a string in a range.
4. Find string for its presence in a list.
5. Find duplicate values in a list.

This function is quick rather than using conventional for loop to search for complete list of cells.