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 WORKBOOK


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

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin