Ucase (VBA) - If "MS" then change color

[Nik_AU]

Hi,

I have at the moment (from anotehr project the below code);

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("A1:K34")) Is Nothing Then
        Select Case UCase(Target)
            Case "W", "W.5"
                icolor = 6
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

Where the W is I want it to see Ms and change the color...but it's not working and I'll explain why..

At the moment the code is looking for an exact match.. so if exact match is MS then color change...

What I have is a list of names that will be pasted into a sheet.. and where the is a Ms (female) I want the color to change

So when I change the W to Ms it only works if the cell has Ms ONLY...if it has Ms E Empty then it does not work?

Anyone have experience with this than I do?

Cheers ahead & have a happy new year.

[royUK]

This works for mee

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("A1:K34")) Is Nothing Then
        Select Case UCase(Target)
            Case "MS"
                icolor = 6
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

[Nik_AU]

Thanks, but thats what I was trying to say in my first post.. if I put "MS", then thats ok if the cell has ONLY ms in it..if I put a name like "Ms Newman" then it does not work because that doce is looking for MS specifically....

I need it to look at a name and if it has Ms in it, then do the action

[VBA Noob]

Maybe

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:K34")) Is Nothing Then
    Select Case True
        Case UCase(Target) Like "*MS*"
        icolor = 6
        Target.Interior.ColorIndex = icolor
    Case Else
        Target.Interior.ColorIndex = xlNone

    End Select
End If
End Sub

VBA Noob

[Nik_AU]

Maybe

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:K34")) Is Nothing Then
    Select Case True
        Case UCase(Target) Like "*MS*"
        icolor = 6
        Target.Interior.ColorIndex = icolor
    Case Else
        Target.Interior.ColorIndex = xlNone

    End Select
End If
End Sub

VBA Noob

OMG that works !!

Thanks a zillion dude !

[VBA Noob]

Your welcome

VBA Noob