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 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: 

Exercise Workbook:

---

This is our initial set of data:

STEP 1: Go to Developer > Code > Visual Basic

 

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

 

STEP 3: Let us test it out!

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

 

Make sure your macro is selected. Click Run.

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

 

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

 

 

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