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() 'Declarations Dim ThisRange As Range Dim ThisCell As Range 'Initialisation 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
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() 'Declaration 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
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 'Declaration Dim Rng As Range 'Initialisation Set Rng = Range("A1:D42") 'Fill blanks cells Rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan 'Free variable Set Rng = Nothing End Sub