Toggle highlighting/unhighlighting cells

[phonesdontfly]

Hi!

I would like to have a macro that I can "toggle" on and off that will highlight all cells that have a user-inputted word.

For instance, if the user inputs Available", then I would like all the cells that contain the word "Available" to highlight yellow. If I run the macro again, I would like them to unhighlight.

I've tried conditional formatting, but that doesn't support the toggle function- it just recreates the same rule over and over with each run of the macro.

Thanks!

 

[CalcSux78]

This may put you on the right path:

Excel 2010

	A	B	C
1	Names	Group	Status
2	JENNY HARVEY	Blue
3	CAROL LYNCH	Magenta
4	EILEEN THOMAS	Blue	Available
5	VICTOR DAVIS	Green
6	BOBBY KELLY	Blue	Available
7	RUTH MORENO	Yellow
8	JIM HUNT	Yellow	Not available
9	GAIL WILSON	Black
10	JANE VASQUEZ	Red	Not avail.
11	MELISSA CASTILLO	Black
12	AGNES DUNCAN	Cyan
13	JOSHUA HAWKINS	Yellow	available.
14	LYNN AGUILAR	Cyan
15	SHANNON JONES	Blue
16	STEVEN FIELDS	Magenta

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Sub ToggleStatus()Dim sStatus As String
Dim rng As Range
Dim cel As Range


sStatus = InputBox(Prompt:="Criteria:", Title:="Highlight Matches", _
        Default:="Available")


Set rng = Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)
For Each cel In rng
    Debug.Print cel.Address
    If InStr(1, cel.Value, sStatus, vbTextCompare) > 0 Then
        With cel.Interior
            If .ColorIndex = 6 Then
                .ColorIndex = -4142
            Else
                .ColorIndex = 6
            End If
        End With
    End If
Next cel


End Sub

The above code should toggle the highlight for any cells in column C matching the search criteria. As is, it is not case sensitive and will look at the contents of each cell to find a match. If you search avail, it will toggle 4,6,8,10,13.

 

[phonesdontfly]

This is fantastic! Thank you! Problem solved!