Excel – VBA – Loop through selection and act based on cell value

Select an area on your spreadsheet. Run this [gs macro] and if it meets certain criteria the cell is highlighted.

You wish to see all cells that contain a certain value. This [gs script] will highlight those cells for you. If you don't make a selection, it will check every cell with a value. Or using the alternate [gs script], you can change all values that meet the [gs criteria] to a different value.

[tab:How to use]

How to use:

Open the Visual Basic Editor by going to Tools-Macro's-Visual Basic Editor or use Alt-F11;

On the toolbar of the Visual Basic Editor, go to Insert - Module;

In the module pane paste the code.

Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close

[tab:Test]

Test the code:

Select the area in this example file of C4-D5 and run the macro by going to file-Macro's-Macro and select the Check_Values_1 macro and click on run.

The cells with a value of 5 will be highlighted. Remember in this example you must select the range first.

The alternate code will allow you to set the range so you don't have to select a range and shows how to replace one value with another.

[tab:The Code]

Place this code in a standard module:

Option Explicit
'this line makes it cap insensitive in your string selection
Option Compare Text
Sub Check_Values_1()
On Error Resume Next
Dim CurCell As Range
Dim Heading As String
Dim Prompt As String
Dim Criteria As Variant
Dim Color As Long
Dim lRows As Long
Dim lCols As Long
Dim lAllCells As Long
lRows = ActiveSheet.Rows.Count
lCols = ActiveSheet.Columns.Count
lAllCells = lRows * lCols
' Ensure that that the entire sheet was not selected
' This would slow the loop down considerably
If Selection.Cells.Count = lAllCells Then
MsgBox "To check the entire sheet, please select only one cell", 64
Exit Sub
End If
' Optional User Config
Heading = "Enter Criteria"
Prompt = "Enter the value you want to find and highlight."
Color = 6
' Get the value of the cell to highlight
Criteria = InputBox(Prompt, Heading)
' Inspect the value to determine type or exit
If Criteria = "" Then
Exit Sub
ElseIf IsNumeric(Criteria) Then
Criteria = CLng(Criteria)
ElseIf IsDate(Criteria) Then
Criteria = CDate(Criteria)
Else
Criteria = CStr(Criteria)
End If
' Loop through each cell in the selection and color
' as desired
If Selection.Cells.Count > 1 Then
For Each CurCell In Selection
If CurCell.Value = Criteria Then CurCell.Interior.ColorIndex = Color
Next CurCell
Else
For Each CurCell In ActiveSheet.UsedRange
'If you don't make a selection, it checks all cells on the sheet with values
If CurCell.Value = Criteria Then CurCell.Interior.ColorIndex = Color
Next CurCell
End If
End Sub

[tab:END]