Search Macro with Cell text colour replacement

[Richard Sharpe]

This is a similar question to one posted by tobiasescalante. Thanks to Frank Kabel for his original reply to my email
Ok- here's the question. I have a single sheet excel file and I want to search through column G for
number of key words - e.g. open, closed, assigned, submitted, duplicate etc et
For each occurence of "opened" I want to change the text on th
corresponding cell in Column E to Red. For each occurrence of "closed
I want to change column E cell text to Green, etc, etc.
I can't use Conditional Formatting as I have more than 3 options

Any help much appreciated. Thanks

 

[Frank Kabel]

Hi
If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell E1:E100 based on the value
in G1:G100 value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("G1:G100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
Select Case .Value
Case "Opened": .offset(0,-2).Interior.ColorIndex = 3
Case "Closed": .offset(0,-2).interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

This is a similar question to one posted by tobiasescalante. Thanks

to Frank Kabel for his original reply to my email.

Ok- here's the question. I have a single sheet excel file and I want

to search through column G for a

 

[Richard Sharpe]

Frank - thanks very much. I changed the line
If Target.Cells.Count > 1 Then Exit Sub t

If Target.Cells.Count < 1 Then Exit Sub and it worked a treat.
I assume that was a typo
Excellent fix - well done

 

[Frank Kabel]

Hi
normally you should not change this
This just assures that the macro is only triggered if only one cell is
changed and not multiple. But in your case also the change will work