Worksheet Change Multiple conditional format

[Range]

I am using the below code and finding that I get a mismatch type error everytime I try to update the sheet with multiple entries - would there be any reason for this? I also have the following queries.

When using this kind of code can I drag cells???
Can i include some code to update font color. and cell patterns?? Thanks

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Long
    Select Case Target.Value
        Case "X03", "X04", "X09", "L35", "X37", "X45", "X70"
            iColor = 3
        Case "121", "MTG"
            iColor = 15
        Case "LUN", "BRK"
            iColor = 7
        Case "UPD"
            iColor = 8
        Case "XFP", "ESD", "PFX", "EVC", "MBX"
            iColor = 17
        Case "TRN", "CHG"
            iColor = 12
        Case "PHN"
            iColor = 8
 
    End Select
 
    Target.Interior.ColorIndex = iColor
End Sub

 

[VoG]

That looks like it should work. When the error occurs click debug - which line of code is highlighted?

 

[Range]

Its always the first case that gets highlighted,

regardless of whether I enter X04 or LUN or any other specified fields.... updating 1 cell works fine but when I update more then 1 cell in an action I get the mismatch. Thanks

 

[VoG]

If you are changing multiple cells try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Long, c As Range
For Each c In Target
    Select Case c.Value
        Case "X03", "X04", "X09", "L35", "X37", "X45", "X70"
            iColor = 3
        Case "121", "MTG"
            iColor = 15
        Case "LUN", "BRK"
            iColor = 7
        Case "UPD"
            iColor = 8
        Case "XFP", "ESD", "PFX", "EVC", "MBX"
            iColor = 17
        Case "TRN", "CHG"
            iColor = 12
        Case "PHN"
            iColor = 8
    End Select
    c.Interior.ColorIndex = iColor
Next c
End Sub

 

[Range]

Thanks that worked a treat!!!! Thanks soo much!!!

Do you know if there is any way that I can make the code non-caps sensitive?

 

[VoG]

Try

 Select Case Ucase(c.Value)

</pre>

 

[Range]

Worked another treat, I feel like i'm in a sweet shop!!! Wow, Immense, thanks. You're saving more soo much time!!!

Okay now I reckon I can stump you on this one. It could be a bit of beast!!!!!!

Background
Im basically using this spreadsheet for "15 minute interval schedule management" for 26 employees in my department.

The plan is to have 5 worksheets Mon-Fri that they use to follow what work is required.

However we dont know what the next days work will involve until said day starts at 8am. at 8am we'll have an idea that we want 40% "PHN", 40% "MBX" split across all employee's on varying shifts. Is it easily possible to code something like this in VBA? My VBA is limited but im a paitient man.

Thanks for you help, if im living a pipe dream with this request pls tell me, i do appreciate your help and dont want to take (too much) advantaged!!

Thanks thaanks thanks

 

[VoG]

I suggest that you start a new thread for this different question.