I am proberly being seriously stupid here but i still can't get it to work. I
have tried all the options listed.

I am opening excel,
Clicking view code,
Opening the module,
Copy and pasting the code,
Closing VB,
Changing the fields

But for some reason there is no change in colour. Is there any more
information i need to give to get this to work?

"Rick Rothstein" wrote:

I take this posting back... my originally posted (cryptic) code WORKS
FINE... it DOES NOT fail if a letter different than E, M, L or O is put in
the cell. Even though it was 1:30 in the morning, I should have tested that
original code to see if it worked or not rather than relying on a "guess"
like I did... sorry for any inconvenience.

So, to repeat, the OP can use this code to color his columns (all he has to
do it replace the ColorIndex numbers 15, 3, 7, 6, for the letters E, M, L
and O respectively, with the ColorIndex numbers of his choosing....

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)


"Rick Rothstein" wrote in message
...
Actually, the code I posted has a defect... it will fail if a letter other
than E, M, L or O is put in the cell. To protect against that possibility,
this code would be better (I think this macro now does what your macro
does)...

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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