Highlighting cells containing special text

[aaron8123]

Hi there,

I was wondering whether anyone could help me on a macro I need.

What I want is something that will scan through a complete worksheet
and highlight (bold or something else) any cells that contain a "@"
character, but then when found replace the "@" with a blank,
effectively removing the special character "@" from that cell and
leaving the rest of the cell content intact.

For example:-

If a cell has @found, it should leave it as found, but highlighted
(bold or something similar).

Thanks in advance.

 

[Mike H]

Hi,

Right click your worksheet, view code and paste this in and run it. Beware
it will also remove the @ from email addresses
Sub sonic()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If InStr(1, c.Value, "@") Then
c.Interior.ColorIndex = 3
c.Value = Replace(c.Value, "@", "")
End If
Next
End Sub


Mike

 

[aaron8123]

Hi Mike,

Many thanks for that, it works a treat, and does exactly what I want.

Thanks a bunch.