Sorry, I missed that... I didn't scroll Luke's message down far enough to
see that routine nor did I try to see what your code was actually referring
to.

--
Rick (MVP - Excel)


"L. Howard Kittle" wrote in message
...
I was refering to the little code to make a color index chart 1 through
56...

Sub CreateReference()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = 1
Next
End Sub

As written, it returns all ColorIndex 1 (Black)

Howard

"Rick Rothstein" wrote in message
...
Actually, since this is a Worksheet Change event, I don't think the code
should be looping at all; rather, I think the code should be working on
the Target argument the VB automatically provides for this event. See the
code I posted earlier for my take on how this event code should be
implemented.

--
Rick (MVP - Excel)


"L. Howard Kittle" wrote in message
...
Hi Luke,

A typo I think...Cells(i, 1).Interior.ColorIndex = 1
Should be should be...Cells(i, 1).Interior.ColorIndex = i

Regards,
Howard

"Luke M" wrote in message
...
Correct, you'll need to use VB. But we'll try to help you out. ;-)
First, right click on your sheet tab, view code. Paste the following
in:

'=======
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Define the range you want conditioned
For Each cell In Range("B:B")
x = cell.Value
Select Case x

'Copy this style as needed
Case "Bob"
cell.Interior.ColorIndex = 1
Case "Tom"
cell.Interior.ColorIndex = 2
Case "Smith"
cell.Interior.ColorIndex = 3
'If you want to modify font, use following format:
cell.Font.ColorIndex = 1

End Select
Next cell
Application.EnableEvents = True
End Sub
'===========


You'll notice the part at the beginning where you define the range you
want
to look at. Set this up to include the cells your want "formatted".

Also, the section with the various cases is where you setup your
different
scenarios. The last case has an example of how to define font color.
Note
that colors are deifned via numbers, not by words. If you need to
create a
"key" to help you know which colors go with which numbers, you can run
the
following macro:

'=======
Sub CreateReference()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = 1
Next
End Sub
'=======

Once you're done editing your macro, close the Visual Basic Editor, and
continue as normal in your workbook.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Eddiec" wrote:

I have been trying to find an answer to my question for months, but to
no
avail.

I need to change the colours of cells based on different words I type
in.
For example "Deployed" = Red Cell, "Awaiting" = Blue Cell etc...
Everywhere I
look people say use Conditional Formatting, however, this only allows
three
changes and I have a minimum of 10. From what I can gather I need to
program
some VB but unfortunately VB may as well be "Klingon".

I guess what I am asking is - could anyone provide a quick VB script
that
could allow me to do what I need?

Pleeeeeeeeeeez help