Condional formatting multiple entries

[QuizToon]

Hi All,

I am sure this is a simple answer to those in the now, unfortunately I am not one of them.

I have a spreadsheet that works as a kind of rota. There are 12 venues in a column . I have 19 staff members. 1 staff member cold be in any of the venues on any given day for the week.

All I wan to do is have the cell change colour depending on who's name appears in it. Different colour for each staff member.

So basically a cell colour for each staff member for whenever the appear in the rota.

The rotas are done months in advance so it becomes quite an issue for a large spreadsheet, I thought it might be better suited to VBA.

The range is from I11 to NO26 (there are some blank rows which need to stay in the sheet

Hope this makes sense.

Thanks in advance

 

[Fluff]

You can setup a rule for each person & just use a formula in conditional formatting like
=I11="QuizToon"

 

[QuizToon]

Hi Thanks for the reply,

I was hoping I could do it in VBA

 

[Fluff]

You can, but how would you want the code run?

 

[QuizToon]

HI Fluff,

I was hoping that it would just run automatically after I had inserted the text in the cell. Is this not possible?

 

[Fluff]

You can put this in the sheet module for that sheet

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I11:NO26")) Is Nothing Then
      Select Case LCase(Target.Value)
         Case "bob"
            Target.Interior.ColorIndex = 35
         Case "mary"
            Target.Interior.ColorIndex = 14
      End Select
   End If
End Sub

All the names need to be in lower case, but will not matter how they're entered on the actual sheet.