Your code stopped as soon as it hit an empty cell.

If you didn't want that, you could just use:

Option Explicit
Sub testme()

Dim myCell As Range
Dim rng As Range
Set rng = Range("B4:M46")
For Each myCell In rng.Cells
If LCase(myCell.Value) Like LCase("cheryl*") Then
With myCell.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ElseIf LCase(myCell.Value) Like LCase("Emma*") Then
With myCell.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ElseIf LCase(myCell.Value) Like LCase("Lauren*") Then
With myCell.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next myCell

End Sub

I don't understand what "lose the name, but keep the variable" means.

Maybe...

Option Explicit
Sub testme()

Dim myCell As Range
Dim rng As Range
Set rng = Range("B4:M46")
For Each myCell In rng.Cells
If LCase(myCell.Value) Like LCase("cheryl*") Then
With myCell.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
myCell.Value = Mid(myCell.Value, Len("cheryl") + 1)
ElseIf LCase(myCell.Value) Like LCase("Emma*") Then
With myCell.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
myCell.Value = Mid(myCell.Value, Len("emma") + 1)
ElseIf LCase(myCell.Value) Like LCase("Lauren*") Then
With myCell.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
myCell.Value = Mid(myCell.Value, Len("lauren") + 1)
End If
Next myCell

End Sub

==============

But Peter's code is more easily modified:

Option Explicit

Sub Test()
Dim mycell As Range
Dim rng As Range
Dim vaNames, vaColours
Dim i As Long, v 'As String

' perhaps a 2D array of cell values
vaNames = Array("Cheryl", "Emma", "Lauren")
vaColours = Array(35, 36, 40)

' doesn't get text in formulas, could do without the specialcells
Set rng = Range("B4:M46").cells

For Each mycell In rng
If Len(mycell.Value) Then
v = mycell.Value
For i = lbound(vaNames) To UBound(vaNames)
If LCase(v) Like LCase(vaNames(i)) & "*" Then
mycell.Interior.ColorIndex = vaColours(i)
mycell.Value = Mid(mycell.Value, Len(vaNames(i)) + 1)
'or to remove any leading spaces
mycell.Value = Trim(Mid(mycell.Value, Len(vaNames(i)) + 1))
End If
Next
End If
Next

End Sub



Simon Lloyd wrote:

Hi thanks for the replies!, Dave for some reason your code didnt work or
didnt appear to and Pete yours worked only if the text appeared as typed
in the code, however this shouldn't be a problem as the names are
selected from dropdowns.........what i really do need now is once the
colour has been put in place i need to loose the name but keep the
variable in the cell.
Could you help with that please?

Regards,
Simon

P.S Dave it must be something i have done for your code not to work as
you are usually spot on!

--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=552061

--

Dave Peterson