add colour with macros

[usl]

Hi the last time I asked for help here it was absolute vrilliant, so why not do it again

I need a bit help with a macro.

I got 10 names in the cells A1, B1, C1 etc. all with a different colour
A1 : Paul , cellcolour: yellow
B1 : Stan , cellcolour: red
C1 : Martin, cellcolour: blue
etc......

then I got an range below a timesecdule, now when I type the name in a cell e.g. F15 or within a surtain range I would like the macro to look for the name in A1, B1,C1 etc. if the name is there is shall then add this colour automatcially.

so if I write the name Stan in the cell F15 the backgroundcolour should then be red.

What if i merge the cell F15:F23 with the name Stan, is it then also possible ?

Thanks a lot in advance for all your creative solutions
best regards
usl

 

[usl]

Hi it´s me again,

this is how far I got now and it is working, although but still one small detail missing,

"Select Case rngC.Value"

regarding the select case, it is now looking for the exact Value in the cell,
how Do I change it so that it will still fillout the cell with a colour even if I only write the first name.
Problem is after name Name I got an numbercode but I don´t want to type that aswell

this is tha actual data: "Alex 520"
how do I get it to add thel colour when I type "Alex" ?
I tried with the "*Alex*" but that is not working

her is the macro I use:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EBereich As Range, rngC As Range
Set EBereich = Range("B4:K27")
If Intersect(Target, EBereich) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each rngC In Target.Cells
With rngC
Select Case rngC.Value
Case ""
.Interior.ColorIndex = 0
.Interior.Pattern = 0
.Interior.PatternColorIndex = 41
Case "Alex"
.Interior.ColorIndex = 34
'.Interior.Pattern = xlGray16
.Interior.PatternColorIndex = 41
Case "Anna"
.Interior.ColorIndex = 3
'.Interior.Pattern = xlLightUp
.Interior.PatternColorIndex = 41
Case "Florian"
.Interior.ColorIndex = 35
'.Interior.Pattern = xlChecker
.Interior.PatternColorIndex = 41
Case "Katja"
.Interior.ColorIndex = 26
'.Interior.Pattern = xlSemiGray75
.Interior.PatternColorIndex = 41
Case "Sabrina"
.Interior.ColorIndex = 43
.Interior.Pattern = xlSolid
Case "Thorsten"
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
Case "Steffi"
.Interior.ColorIndex = 45
.Interior.Pattern = xlSolid
Case "Verena"
.Interior.ColorIndex = 39
.Interior.Pattern = xlSolid


End Select
End With
Next rngC
ErrHandler:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub