Method 1 – Using Cell Styles to Highlight Cells
- Select cells >> go to the Home tab >> Styles >> select Cell Styles >> choose a style. Here, Accent1.
This is the output.
Method 2 – Use the Font Color to Highlight a Text Within a Cell
- Press CTRL >> select the target cells one by one >> go to the Home tab >> Font >> set a color.
This is the output.
Method 3 – Applying Format Cells to Highlight an Entire Row
- Click the row index >> press CTRL+1 to display the Format Cells dialog box.
- Go to Fill >> choose a background color >> click OK.
This is the output.
Method 4 – Using a VBA Code to Highlight Alternate Rows in Excel
- Go to the Developer tab >> Visual Basic.
- Open the VB Editor.
- In Insert >> choose Module >> enter the following code in Module1.
Sub ColorEvenRows()
Dim my_Rng As Range
Dim my_i As Integer
Set my_Rng = Selection
For my_i = 1 To Selection.Rows.Count
If (my_Rng.Cells(my_i, 1).Row) Mod 2 = 0 Then
Range(my_Rng.Cells(my_i, 1), my_Rng.Cells(my_i, my_Rng.Columns.Count)). _
Interior.Color = RGB(50, 190, 150)
End If
Next my_i
End Sub
Code Breakdown
- a sub-procedure is created: ColorEvenRows.
- my_Rng is declared as Range and my_i as Integer.
- The Selection property is used: the code will run in the selected cells.
- The For Next loop will go till the last row of the selected cells.
- The If End If statement will check whether the row number is even or odd.
- If the row number is even, the filled cells will be colored with the RGB color combination of 50,190 and 150.
- RGB is a function that defines color by combining red, green, and blue. The RGB function takes three arguments: the red value, the green value, and the blue value. Each of them is ranging from 0 to 255.
- Save the code and go back to the worksheet.
- Developer tab >> go to Macros >> in the Macro dialog box >> name the macro >> click Run.
This is the output.
Method 5 – Using the Find and Replace Feature to Highlight Cells
- Select the whole data >> press CTRL+F to display the Find and Replace dialog box.
- In the Find and Replace dialog box >> go to Find >> in Find what:, enter Pencil >> in Look in, select Values >> click Find All >> press CTRL+A to select the result.
- In the Home tab >> go to Font >> Fill Color >> choose a color. Cells containing “Pencil” are highlighted.
Method 6 – Use the Conditional Formatting to Highlight Cells Based on Criteria in Excel
6.1 Highlight Text Values that contain a feature
- Select the dataset >> in the Home tab >> go to Styles >> Conditional Formatting >> Highlight Cells Rules >> select Text that Contains.
- In the dialog box, enter “Notebook” in Format cells that contain the text >> choose a color >> click OK.
- Cells containing “Notebook” are highlighted.
6.2 Using a New Rule to Highlight Cells Having Values That meet the Formula
- Select the dataset (don’t select the headers) >> in the Home tab >> go to Styles >> Conditional Formatting >> select New Rule.
- In the New Formatting Rule dialog box >> select Use a formula to determine which cells to format >> go to Format values where this formula is true >> enter the formula >> click Format >> go to Fill >> set a color >> click OK to Format Cells >> click OK in New Formatting Rule.
This is the output.
How to Use the Format Painter to Highlight Other Cells
- Select the cell >> in the Home tab >> go Clipboard >> double-click the Format Painter.
- You will see a plus icon with the brush.
- Select cells and click the icon to copy the format.
- Click Format Painter again to stop copying the format.
How to Change the Format Color of a Highlighted Text
- Select the cells you want to change.
- In the Home tab >> go to Font >> Font Color >> set a new color.
How to Highlight an Active Row and Column in Excel
- Right-click the name of the sheet >>in the Context Menu Bar >> select View Code.
- Copy the code to the VB Editor.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
'This will clear all the Fill color.
Cells.Interior.ColorIndex = 0
With Target
.EntireRow.Interior.Color = vbYellow
.EntireColumn.Interior.Color = vbYellow
End With
Application.ScreenUpdating = True
End Sub
Code Breakdown
- This code will work while you change the selection.
- Keeps the interior color of cells 0 to remove the previous color.
- For the active row/column, sets a new color.
Go to the selected sheet and click any cell to highlight the related row and column.
Frequently Asked Questions
1. What is the fastest way to highlight in Excel?
- Use SHIFT+Right/Left/Up/Down Arrow to select adjacent cells.
- Use CTRL key to select nonadjacent cells.
- Use CTRL+SHIFT+Right/Left/Up/Down Arrow to select all cells of the same row/column.
- In Fill Color >> set the color.
2. How do you highlight a column in Excel?
Click the column index number >> in Home tab >> Fill Color >> set the color.
3. How do I highlight part of text in an Excel cell?
Select that cell >> go to the Formula Bar >> select the text portion >> Font Color >> choose a color.
How to Highlight in Excel: Knowledge Hub
- Highlight Lowest Value in Excel
- Highlight Highest Value in Excel
- Highlight Text in Excel
- Highlight a Column
- Compare Two Excel Sheets and Highlight Differences
- Highlight Text in Text Box
- Highlight a Cell
Download Practice Workbook
Download the Excel file and practice.
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!