Probably a little more cryptic than yours, but a little shorter...

Sub ColorColumns()
Dim C As Range
For Each C In Worksheets("Sheet1").Range("B4:P4")
C.Offset(2).Resize(20).Interior.ColorIndex = Choose(InStr(1, _
" EMLO", Left(C.Value & " ", 1), vbTextCompare), _
xlColorIndexNone, 15, 3, 7, 6)
Next
End Sub

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Hi Dan; try the below//

Sub Macro()
Dim colIndex As Variant
For Each cell In Range("B4:P4")
colIndex = Empty
Select Case UCase(cell.Text)
Case "E"
colIndex = 15
Case "M"
colIndex = 3
Case "L"
colIndex = 7
Case "O"
colIndex = 6
End Select

If colIndex = Empty Then colIndex = -4142
Range(Cells(6, cell.Column), Cells(25, _
cell.Column)).Interior.ColorIndex = colIndex
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Dan Wood" wrote:

I am really struggling to create a macro in VB.

I need to set the macro to colour cells dependant on what character is in
the title cell.

In cells B4 to P4 therewill be characters E, M, L or O
In cells B6 down to B25 and across from there to P6:P25 is a range of
cells
that need to be formatted with certain colours. For example if in B4 it
says
E i need the cells in B6 to B25 to fill in blue.

I believe i need to set a range of cells, then refer to them to llok
something like this:-

For Each cell In SHIFT_range
Select Case LCase(cell.Value)
Case ""
cell.Interior.ColorIndex = 0
Range(cell.Offset(0, -1), cell.Offset(0,
60)).Interior.ColorIndex = 0

Case "o"
Range(cell, cell.Offset(0, -1)).Interior.ColorIndex = 15
Range(cell.Offset(0, 1), cell.Offset(0,
60)).Interior.ColorIndex
= 0

But as stated above i am really struggling with this. Any help is
gratfully
received