When working on your Excel spreadsheet, you often need to highlight certain data.

Below are some macro procedures that will allow you to highlight specified data using VBA code.

Highlight Duplicates values in VBA

When studying your data you can notice many duplicates. Imagine that, you don’t want to delete them with a remove duplicate but simply highlight them.

How to highlight duplicate values in your sheet of your workbook? To do this, simply select the range of cells concerned, loop through the cells and use a CountIf function nested in an if to check if the element appears more than once. The value will be highlighted by the RGB code you choose.

Sub HighlightDuplicateValues()

    Dim ThisRange As Range
    Dim ThisCell As Range
    Set ThisRange = Range("A1:D150")
    'Loop on cells in the range
    For Each ThisCell In ThisRange
        If WorksheetFunction.CountIf(ThisRange, ThisCell.Value2) > 1 Then
            ThisCell.Interior.Color = RGB(131, 20, 49) 'colour cell
        End If
    Next ThisCell
   'Free variables
   Set ThisRange = Nothing

End Sub

Duplicates values

Highlight Misspelled Word in VBA

On your Excel spreadsheet, you realize that many words are misspelt and you would like to highlight them to correct them. For this, you must use the VBA CheckSpelling function which requires a cell or range value as an argument. You can then colour using the range style method in green or red if the spelling is good or wrong.

Sub HighlightMisspelledWordInCell()

    Dim rng As Range
    'Loop on cell in a range of the active sheet
    For Each rng In ActiveSheet.UsedRange
        'Check the spelling of the word (if not correct)
        If Not Application.CheckSpelling(Word:=rng.Text) Then rng.Style = "Bad"
        'Check the spelling of the word (if correct)
        If Application.CheckSpelling(Word:=rng.Text) Then rng.Style = "Good"

    Next rng
End Sub

Misspelled word

Highlight Empty Cells in VBA

To highlight the blank values of a cell range without wasting time with a loop, the most effective method is to use the SpecialCells property with xlCellTypeBlanks to colour these values.

Sub HighlightEmpty()
'Highlight all empty cells

    Dim Rng As Range
    Set Rng = Range("A1:D42")
    'Fill blanks cells
    Rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan

    'Free variable
    Set Rng = Nothing

End Sub
Highlight blank or empty cell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s