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
Names	Group	Status
JENNY HARVEY	Blue
CAROL LYNCH	Magenta
EILEEN THOMAS	Blue	Available
VICTOR DAVIS	Green
BOBBY KELLY	Blue	Available
RUTH MORENO	Yellow
JIM HUNT	Yellow	Not available
GAIL WILSON	Black
JANE VASQUEZ	Red	Not avail.
MELISSA CASTILLO	Black
AGNES DUNCAN	Cyan
JOSHUA HAWKINS	Yellow	available.
LYNN AGUILAR	Cyan
SHANNON JONES	Blue
STEVEN FIELDS	Magenta

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

</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!