Pinterest Pixel

Highlight and Count a Specified Value Using Macros In Excel

Bryan
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.

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


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


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Convert Chart into Image Using Macros In Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

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