Excel 2003: VBA script to colour cell that contains part of text

[garoe]

Hi,
I am trying to identify a script that will enable me to have a cell coloured in, based on that cell containing a text string, or alternatively part of a text string. I can't use conditional formatting as there are 5 possible conditions.

I have tried to use the Private Sub Worksheet_Change(ByVal Target As Range) script, but I am obviously doing something wrong.

Attached is a sample, which also include the script.

To explain; columns D and E will be hidden, but this is where the content for columns A and B will gather their information from. The codes in column D appear as a drop down menu in column A, and column B is populated based on column A, looking up information in column E.

What I want to happen is that when someone selects a code in the drop down menu on column A, the cell is coloured as per column D.

I have tested the scrip using numerics, and it works then, however we have a set of codes which are formatted in the same way as the sample, with hyphens etc, so I need to be able to have a script that can work on this type of data.

I hope this all makes sense, and I hope someone out there can provide me with some help!

[RHCPgergo]

Hi garoe,

welcome to the forum.
There's only a simple mistake in your code, you didn't include a space in the string, so they are not the same. So

Case "A01 - code name#1": NewColor = 37

should be

Case "A01 - code name #1": NewColor = 37

[RHCPgergo]

Also, consider this solution and feel free to adapt it to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim I As Range
Dim Rng As Range

Set I = Intersect(Target, Range("A2:A80"))
If Not I Is Nothing Then
    For Each Rng In Range("D1:D20")
        If Rng.Value = Target.Value Then
            Target.Interior.ColorIndex = Rng.Interior.ColorIndex
            Exit For
        End If
    Next Rng
End If
End Sub