Highlight many more macros

Highlight Duplicates from Selection

This macro will check each cell of your selection and highlight the duplicate values. You can also change the color from the code.

Sub HighlightDuplicateValues()

Dim myRange As Range

Dim myCell As Range

Set myRange = Selection

For Each myCell In myRange

If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then

myCell.Interior.ColorIndex = 36

End If

Next myCell

End Sub

Highlight the Active Row and Column

I really love this macro code whenever I have to analyze a data table. Here are the quick steps to apply this code.

1.     Open VBE (ALT + F11).

2.     Go to Project Explorer (Ctrl + R, If hidden). Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro.

3.     Paste the code into it and select the “BeforeDoubleClick” from event drop down menu.

4.     Close VBE and you are done.

Remember that, by applying this macro you will not able to edit the cell by double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim strRange As String

strRange = Target.Cells.Address & "," & _

Target.Cells.EntireColumn.Address & "," & _

Target.Cells.EntireRow.Address

Range(strRange).Select

End Sub

Highlight Top 10 Values

Just select a range and run this macro and it will highlight top 10 values with the green color.

Sub TopTen()

Selection.FormatConditions.AddTop10

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.TopBottom = xlTop10Top

.Rank = 10

.Percent = False

End With

With Selection.FormatConditions(1).Font

.Color = -16752384

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 13561798

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

End Sub

Highlight Named Ranges

If you are not sure about how many named ranges you have in your worksheet then you can use this code to highlight all of them.

Sub HighlightRanges()

Dim RangeName As Name

Dim HighlightRange As Range

On Error Resume Next

For Each RangeName In ActiveWorkbook.Names

Set HighlightRange = RangeName.RefersToRange

HighlightRange.Interior.ColorIndex = 36

Next RangeName

End Sub

Highlight Greater Than Values

Once you run this code it will ask you for the value from which you want to highlight all greater values.

Sub HighlightGreaterThanValues()

Dim i As Integer

i = InputBox("Enter Greater Than Value", "Enter Value")

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=i

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.Font.Color = RGB(0, 0, 0)

.Interior.Color = RGB(31, 218, 154)

End With

End Sub

Highlight Lower Than Values

Once you run this code it will ask you for the value from which you want to highlight all lower values.

Sub HighlightLowerThanValues()

Dim i As Integer

i = InputBox("Enter Lower Than Value", "Enter Value")

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLower, Formula1:=i

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.Font.Color = RGB(0, 0, 0)

.Interior.Color = RGB(217, 83, 79)

End With

End Sub

Highlight Negative Numbers

Select a range of cells and run this code. It will check each cell from the range and highlight all cells the where you have a negative number.

Sub highlightNegativeNumbers()

Dim Rng As Range

For Each Rng In Selection

If WorksheetFunction.IsNumber(Rng) Then

If Rng.Value < 0 Then

Rng.Font.Color= -16776961

End If

End If

Next

End Sub

Highlight Specific Text

Suppose you have a large dataset and you want to check for a particular value. For this, you can use this code. When you run it, you will get an input box to enter the value to search for.

Sub highlightValue()

Dim myStr As String

Dim myRg As Range

Dim myTxt As String

Dim myCell As Range

Dim myChar As String

Dim I As Long

Dim J As Long

On Error Resume Next

If ActiveWindow.RangeSelection.Count> 1 Then

myTxt= ActiveWindow.RangeSelection.AddressLocal

Else

myTxt= ActiveSheet.UsedRange.AddressLocal

End If

LInput: Set myRg= Application.InputBox("please select the data range:", "Selection Required", myTxt, , , , , 8)

If myRg Is Nothing Then

Exit Sub

If myRg.Areas.Count > 1 Then

MsgBox"not support multiple columns" GoToLInput

End If

If myRg.Columns.Count <> 2 Then

MsgBox"the selected range can only contain two columns "

GoTo LInput

End If

For I = 0 To myRg.Rows.Count-1

myStr= myRg.Range("B1").Offset(I, 0).Value

With myRg.Range("A1").Offset(I, 0)

.Font.ColorIndex= 1

For J = 1 To Len(.Text)

Mid(.Text, J, Len(myStr)) = myStrThen

.Characters(J, Len(myStr)).Font.ColorIndex= 3

Next

End With

Next I

End Sub

Highlight Cells with Comments

To highlight all the cells with comments use this macro.

Sub highlightCommentCells()

Selection.SpecialCells(xlCellTypeComments).Select

Selection.Style= "Note"

End Sub

Highlight Alternate Rows In The Selection

By highlighting alternate rows you can make your data easily readable. And for this, you can use below VBA code. It will simply highlight every alternate row in selected range.

Sub highlightAlternateRows()

Dim rng As Range

For Each rng In Selection.Rows

If rng.RowMod 2 = 1 Then

rng.Style= "20% -Accent1"

rng.Value= rng^ (1 / 3)

Else

End If

Next rng

End Sub

Highlight Cells With Misspelled Words

If you find hard to check all the cells for spelling error then this code is for you. It will check each cell from the selection and highlight the cell where is a misspelled word.

Sub HighlightMisspelledCells()

Dim rng As Range

For Each rng In ActiveSheet.UsedRange

If Not Application.CheckSpelling(word:=rng.Text) Then

rng.Style= "Bad" End If

Next rng

End Sub

Count/Highlight Cells With Error In Entire Worksheet

To highlight and count all the cells in which you have an error, this code will help you. Just run this code and it will return a message with the number error cells and highlight all the cells.

Sub highlightErrors()

Dim rng As Range

Dim i As Integer

For Each rng In ActiveSheet.UsedRange

If WorksheetFunction.IsError(rng) Then

i = i + 1 rng.Style = "bad"

End If

Next rng

MsgBox "There are total " & i & " error(s) in this worksheet."

End Sub

Count/Highlight Cells With A Specific In Entire Worksheet

This code will help you to count the cells which have a specific value which you will mention and after that highlight all those cells.

Sub highlightSpecificValues()

Dim rng As Range

Dim i As Integer

Dim c As Variant

c = InputBox("Enter Value To Highlight")

For Each rng In ActiveSheet.UsedRange

If rng = c Then

rng.Style = "Note"

i = i + 1

End If

Next rng

MsgBox "There are total " & i &" "& c & " in this worksheet."

End Sub

Highlight all the Cells in a Worksheet which are Blank but have an Invisible Space

Sometimes there are some cells which are blank but they have a single space. And, due to this, it’s really hard to identify them. This code will check all the cell in the worksheet and highlight all the cells which have a single space.

Sub blankWithSpace()

Dim rng As Range

For Each rng In ActiveSheet.UsedRange

If rng.Value = " " Then

rng.Style = "Note"

End If

Next rng

End Sub

Highlight Max Value In The Range

It will check all the selected cells and highlight the cell with the maximum value.

Sub highlightMaxValue()

Dim rng As Range

For Each rng In Selection

If rng = WorksheetFunction.Max(Selection) Then

rng.Style = "Good"

End If

Next rng

End Sub

Highlight Min Value In The Range

It will check all the selected cells and highlight the cell with the Minimum value.

Sub highlightMinValue()

Dim rng As Range

For Each rng In Selection

If rng = WorksheetFunction.Min(Selection) Then

rng.Style = "Good"

End If

Next rng

End Sub

Highlight Unique Values

This codes will highlight all the cells from the selection which has a unique value.

Sub highlightUniqueValues()

Dim rng As Range

Set rng = Selection

rng.FormatConditions.Delete

Dim uv As UniqueValues

Set uv = rng.FormatConditions.AddUniqueValues

uv.DupeUnique = xlUnique

uv.Interior.Color = vbGreen

End Sub

Highlight Difference In Columns

Using this code you can highlight the difference between two columns (corresponding cells).

Sub columnDifference()

Range("H7:H8,I7:I8").Select

Selection.ColumnDifferences(ActiveCell).Select

Selection.Style= "Bad"

End Sub

Highlight Difference In Rows

And, by using this code you can highlight difference between two row (corresponding cells).

Sub rowDifference()

Range("H7:H8,I7:I8").Select

Selection.RowDifferences(ActiveCell).Select

Selection.Style= "Bad"

End Sub