Coloring a range of cells in Excel using VBA

Question

I have a spreadsheet that I use at work to keep track of returned contracts. I've set up a color key so I can quickly glance at the sheet to see which contracts still need to be returned to us and which account manager the account belongs to.

The account manager's initials are listed in column A; from there I would like to color the range of cells in that row (A:H) depending on who's initials are entered in A. Right now I have the following code in place, but I don't like the way the spreadsheet looks with the entire row colored:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 1 Then Exit Sub

Select Case Target

Case "MKH"
Target.EntireRow.Interior.ColorIndex = 36

Case "MAH"
Target.EntireRow.Interior.ColorIndex = 39

Case "MJM"
Target.EntireRow.Interior.ColorIndex = 34

Case "JVE"
Target.EntireRow.Interior.ColorIndex = 35

Case Else
Target.EntireRow.Interior.ColorIndex = 0

End Select

P.S. The majority of workers in our office are still using Office 2003, so conditional formatting is not an option.

Answer 1

If your question is how to color a part of the row instead of the whole one, this should work:

Range("A" & Target.Row, "H" & Target.Row).Interior.ColorIndex = 10