Pinterest Pixel

Highlight and Count a Specified Value Using Macros In Excel

Let us try something fun using Excel Macros. We want to highlight a specified value from the... read more

Download Excel Workbook
Bryan
Posted on

Overview

Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline

Let us try something fun using Excel Macros. We want to highlight a specified value from the user, then show the count as well! You will learn how to show a message back as well to your Excel user.

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Highlight and show the count of a specified value

Copy Source Code:


Sub HighlightAndCountSpecifiedValue()

Dim cell As Range
Dim counter As Integer
Dim specificValue As Variant

'Get the specific value from the user
specificValue = InputBox("Enter Value To Highlight", "Enter Value")

For Each cell In ActiveSheet.UsedRange

'If it matches the specified value, then change the style to be Note
If cell = specificValue Then
cell.Style = "Note"
counter = counter + 1
End If
Next cell

'Show the total count to the user
MsgBox "There are a total of " & counter &" "& specificValue & " in this worksheet."

End Sub

Final Result: 

Highlight and Count a Specified Value Using Macros In Excel

Exercise Workbook:

Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline

Download excel workbookHighlight-and-Count-a-Specified-Value.xlsm

See also  Save Each Worksheet as a PDF File Using Macros In Excel


This is our initial set of data:

Highlight and Count a Specified Value Using Macros In Excel

STEP 1: Go to Developer > Code > Visual Basic

Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Highlight and Count a Specified Value Using Macros In Excel

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Highlight and Count a Specified Value Using Macros In Excel

Type in the value to highlight and count. Let us type in Ian Wright. Click OK.

Highlight and Count a Specified Value Using Macros In Excel

 

With just one click, all of the Ian Wright values are now highlighted and you see the count as well!

 Highlight and Count a Specified Value Using Macros In Excel

 

How to Highlight and Count a Specified Value Using Macros In Excel

 

101 Macros Book

If you like this Excel tip, please share it
Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline Highlight and Count a Specified Value Using Macros In Excel | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...