If you want to know how many times a specific value entered into the worksheet, what would you do? In this article, we are going to see how to highlight & count a specified value using Macros (VBA) in Excel Office 365. You just follow the instructions given below and complete your work quickly.
Highlight & Count a Specified Value using Macros:
To highlight and find the cells with the specified value, follow the below instructions.
- For example, you can see the range of data B1:B8 in the following image. We are going to find the specific value from the range.
- On the Developer Tab, select the Visual Basic option under the Code section.
- Then you need to copy and paste the code given below.
VBA code to highlight and count the specific value using Macros
Sub HighlightAndCountSpecifiedValue()
Dim cell As Range
Dim counter As Integer
Dim specificValue As Variant
specificValue = InputBox(“Enter Value To Highlight”, “Enter Value”)
For Each cell In ActiveSheet.UsedRange
If cell = specificValue Then
cell.Style = “Note”
counter = counter + 1
End If
Next cell
MsgBox “There are a total of ” & counter &” “& specificValue & ” in this worksheet.”
End Sub
- You need to save the code by selecting it. Then close the window.
- You have to open the sheet containing the data. On the Developer Tab, choose the Macros option in the Code section.
- You need to make sure that your macro is selected and click the Run button.
- Then it will display Enter the value dialog box. You need to enter the value that you want to highlight and count in the selection.
- Now, you will get the result as shown in the below image.
Sum-Up:
In this article, you can easily learn the clear-cut steps o highlight & count a specified value using Macros in Excel Office 365. Kindly, give your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!
Top comments (0)