VBA Help: highlight/color cell if cell contains text

[Legacy 191833]

Pulling my hair out over this one. I am trying to run a script to change the cell color if the cell contains (not "equal to") text.

Currently, I'm the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:L20")) Is Nothing Then
Select Case Target
Case "AA"
icolor = 1
Case "BB"
icolor = 2
Case "CC"
icolor = 3
Case "DD"
icolor = 4
Case "EE"
icolor = 5
Case "FF"
icolor = 6
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

which worked fine, but now the information contained in the cells is not limited to "AA", "BB", etc. They now have numeric prefixes (e.g. "101 AA", "102 AA") and I still need to identify (color code) cells based on them containing their alphabetic identifers with all "XX AA" same color, all "XX BB" same color, etc.

Thanks in advance, gang!

 

[Rick Rothstein]

Does putting this statement immediately before your Select Case statement make things work as you want?

If UBound(Split(Target.Value)) <> 2 Then Exit Sub

 

[Legacy 191833]

No, it's still behaving the same. If I input my data into a separate sheet/book and populate the cell using the formula above, it does not work. However, if I input the same value directly into the cell, the color-coding works perfectly.

 

[Rick Rothstein]

I think we are going to need to see your workbook directly in order to troubleshoot this any further. Are you willing to post your workbook to one of the free file sharing websites on the Internet? If so, you can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[RoryA]

A change event will not be triggered by formulas, so you need the calculate event instead, and you will have to loop through all the cells of interest.