How to Search Excel Range and Format by VBA InStr/EntireRow
In this short tutorial, we will show you how to search text in Excel Ranges (Cells, Rows), etc., and then format entire rows.
Search Excel Range and Format Rows
In this short tutorial, we will show you how to search text in Excel Ranges (Cells, Rows), etc., and then format entire rows.
The example covers font color, bold, italic, and other formatting based on searched data.
Following is the sample sheet we will use for the examples:
Making text red for a range cell by InStr
In this example, we created a range of “Product Name” column, which is B in our sample sheet.
We iterated through the range by For..Each loop
In each iteration, we will execute the InStr function and search for the product that starts with the letter “T”.
For each product found, we will change the font color to red by Font.ColorIndex property:
VBA code:
Sub search_format()
Dim Rng_fmt As Range
Dim cell As Range
Dim instr_res
'Creating range of cells
Set Rng_fmt = Range("B2:B11")
For Each cell In Rng_fmt
instr_res = InStr(cell, "T")
If Not instr_res = 0 Then
'Making all cells red with letter 'T'
cell.Font.ColorIndex = 3
End If
Next cell
End Sub
Result
Formatting whole row example
Now, we will search in the fourth column (D) i.e. Status
We will find all cells with “Out of Stock” status by the InStr function.
For each cell found with “Out of Stock”, we will change the row.
The cell.EntireRow.Font.ColorIndex property is used to change the color of the whole row.
VBA code:
Sub search_format()
Dim Rng_fmt As Range
Dim cell As Range
Dim instr_res
'Range of cells based on D column
Set Rng_fmt = Range("D2:D11")
For Each cell In Rng_fmt
instr_res = InStr(cell, "Out")
If Not instr_res = 0 Then
'Applying color, bold and italic to whole row
cell.EntireRow.Font.ColorIndex = 5
End If
Next cell
End Sub
Result:
Making text bold and italic for the entire row
Similarly, you may format the row’s text to bold, italic, etc. along with changing the color.
The below example is an extension to the above for making text Bold and Italic:
Code:
Sub search_format()
Dim Rng_fmt As Range
Dim cell As Range
Dim instr_res
'Range of cells based on D column
Set Rng_fmt = Range("D2:D11")
For Each cell In Rng_fmt
instr_res = InStr(cell, "Out")
If Not instr_res = 0 Then
'Applying color, bold and italic to whole row
cell.EntireRow.Font.Bold = True
cell.EntireRow.Font.Italic = True
cell.EntireRow.Font.ColorIndex = 7
End If
Next cell
End Sub
Output:
