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

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

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

 

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

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

 

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

 

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

 

HELPFUL RESOURCE:

101 Macros Book

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn