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]

Try changing your Select Case statement to this...

Select Case Right(Target.Value, 2)

 

[Legacy 191833]

Perfect!

And what would the code look like if the entry were something like "101 AA 101" where the identifier were preceding a space and 3 digit part number?

 

[Rick Rothstein]

Perfect!

And what would the code look like if the entry were something like "101 AA 101" where the identifier were preceding a space and 3 digit part number?

That is more of a problem to handle. As long there were always a space and 3 digits after the letters whenever there was something following the letters, then you could declare a String variable, say named TestVal, and then use that in the Select Case statement ...

If Right(Target.Value, 1) Like "#" Then
  TestVal = Mid(Target.Value, Len(Target.Value) - 5, 2)
Else
  TestVal = Right(Target.Value, 2)
End If
Select Case TestVal

 

[RoryA]

You might also use Split and take the second item of the array result.

 

[Legacy 191833]

You might also use Split and take the second item of the array result.

I think this is exactly what I need. The content may vary but it will always look like "(some text) AA (some text)". How would I go about using spilt in this situation?

My coding isn't as strong as it used to be. Thanks for all the help!

 

[Rick Rothstein]

I think this is exactly what I need. The content may vary but it will always look like "(some text) AA (some text)". How would I go about using spilt in this situation?

My coding isn't as strong as it used to be. Thanks for all the help!

As long as your first "some text" never contains any spaces...

Select Case Split(Target.Value)(1)

That was a good idea Rory! (Assuming, of course, that there is only that one space in front of the 2-character codes.)

 

[Legacy 191833]

Perfect! That is exactly what I needed! Thank you so much everyone!

 

[RoryA]

Assuming no spaces within the surrounding text items:

strCheck = split(application.trim(target.value))(1)

as a simple example (the Trim is in case of errant double spaces between text)

Edit: bit late back I see - caught up in baby duties

 

[Legacy 191833]

OK, so when I tested it in a blank book, this worked great:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:F60")) Is Nothing Then
Select Case Split(Target.Value)(1)
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

BUT when I put it into my actual working book it didn't work at all and I think I know why. The cells to be color-coded are populated by:

='List1'!$B2&" "&'List2'!$B2&" "&'List3'!$B2

When I input the data manually, the above VBA code works perfectly, but I input data separately across the 3 lists. Is there a way to get the code to only run after each cell on the compiled data sheet is fully populated? Or as soon as it sees the 2nd element of the cell/array is "AA", "BB", etc. it changes the cell to the corresponding color?

Thanks again, folks!