Re: Have row color change to one of 8 different colors based on one ce

works great. Missed that one.

One last question on this. Is there a way to have this applied
regardless if the letter is 'cap' or 'lower case'? Right now it is set
to caps but I would like this to run either way.
thx

Mel


PCLIVE (RemoveThis) wrote:
If you want it to change upon entering it into the cell, you'll need to
utilize the code from Gary''s Student. It sounds like you may have already
done that. If you want to get the row to change as soon as you enter the
Letter code, you may need to use Worksheet_Change instead of
Worksheet_SelectionChange. To enter this code, right-click on the worksheet
tab and selecting view code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I2:I65536")) Is Nothing Then
Exit Sub
End If

v = Target.Value

Select Case v
Case "I"
Target.EntireRow.Interior.ColorIndex = 4
Case "O"
Target.EntireRow.Interior.ColorIndex = 5
Case "C"
Target.EntireRow.Interior.ColorIndex = 6
Case "T"
Target.EntireRow.Interior.ColorIndex = 7
Case "L"
Target.EntireRow.Interior.ColorIndex = 8
Case "E"
Target.EntireRow.Interior.ColorIndex = 9
Case "X"
Target.EntireRow.Interior.ColorIndex = 10
Case "A"
Target.EntireRow.Interior.ColorIndex = 11
End Select
End Sub


"Mel" <pilgrimm@xxxxxxxxx> wrote in message
news:1160082129.148603.159710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Worked great.

One thing I did notice is that when I type in the cell value in column
'I', it does not change the row color unless I move the curser up to
that cell after typing in it. Seems like I have to move down one row,
then go back up to the cell in 'I' for the color to change.
Should it not change the row color right away?

thx again.

Mel


PCLIVE (RemoveThis) wrote:
Press Alt+F11 to bring up the VB editor.
Right-Click on "ThisWorkbook". Goto "Insert" and then select "Module".
Paste the following code:

Sub ColorRow()

For Each cell In Range("I2:I" & Range("I65536").End(xlUp).Row)
If cell.Value = "I" Then cell.EntireRow.Interior.ColorIndex = 1
If cell.Value = "O" Then cell.EntireRow.Interior.ColorIndex = 2
If cell.Value = "C" Then cell.EntireRow.Interior.ColorIndex = 3
If cell.Value = "T" Then cell.EntireRow.Interior.ColorIndex = 4
If cell.Value = "L" Then cell.EntireRow.Interior.ColorIndex = 5
If cell.Value = "E" Then cell.EntireRow.Interior.ColorIndex = 6
If cell.Value = "X" Then cell.EntireRow.Interior.ColorIndex = 7
If cell.Value = "A" Then cell.EntireRow.Interior.ColorIndex = 8
Next cell

End Sub


Then all you need to do is run the macro.
HTH,
Paul



"Mel" <pilgrimm@xxxxxxxxx> wrote in message
news:1160074209.625555.126210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Here's what I have so far but still can't seem to get it to work.
Not sure what I am missing. thx

For Each cell In Range("I1:I65536")
If cell.Value = "I" Then cell.EntireRow.Interior.ColorIndex = 1
If cell.Value = "O" Then cell.EntireRow.Interior.ColorIndex = 2
If cell.Value = "C" Then cell.EntireRow.Interior.ColorIndex = 3
If cell.Value = "T" Then cell.EntireRow.Interior.ColorIndex = 4
If cell.Value = "L" Then cell.EntireRow.Interior.ColorIndex = 5
If cell.Value = "E" Then cell.EntireRow.Interior.ColorIndex = 6
If cell.Value = "X" Then cell.EntireRow.Interior.ColorIndex = 7
If cell.Value = "A" Then cell.EntireRow.Interior.ColorIndex = 8
Next cell


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Intersect(Target, Range("I2:I65536")) Is Nothing Then
Exit Sub
End If


v = Target.Value


Select Case v
Case "I"
Target.EntireRow.Interior.ColorIndex = 4
Case "O"
Target.EntireRow.Interior.ColorIndex = 5
Case "C"
Target.EntireRow.Interior.ColorIndex = 6
Case "T"
Target.EntireRow.Interior.ColorIndex = 7
Case "L"
Target.EntireRow.Interior.ColorIndex = 8
Case "E"
Target.EntireRow.Interior.ColorIndex = 9
Case "X"
Target.EntireRow.Interior.ColorIndex = 10
Case "A"
Target.EntireRow.Interior.ColorIndex = 11
End Select
End Sub







Gary''s Student wrote:
Put the following in work*** code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("I2:I65536")) Is Nothing Then
Exit Sub
End If

v = Target.Value

Select Case v
Case "I"
Target.EntireRow.Interior.ColorIndex = 4
Case "O"
Target.EntireRow.Interior.ColorIndex = 5
Case "C"
Target.EntireRow.Interior.ColorIndex = 6
Case "T"
Target.EntireRow.Interior.ColorIndex = 7
Case "L"
Target.EntireRow.Interior.ColorIndex = 8
Case "E"
Target.EntireRow.Interior.ColorIndex = 9
Case "X"
Target.EntireRow.Interior.ColorIndex = 10
Case "A"
Target.EntireRow.Interior.ColorIndex = 11
End Select
End Sub

It will automatically re-color the entire row based on the value in
column I.

REMEMBER work*** code.
--
Gary''s Student


"Mel" wrote:

Have a spread*** with many lines on it.

We have one cell (starting on row 2, column I) the cell will have
one
of 8 different values. (I, O, C, T, L, E, X, A).
Depending on which one, would like that row to be set to change
background color depending on value in cell I2, i3, etc. as you go
down.

Base color is blank (white).

I know I cannot use conditional formatting as I have more than 3
colors.
Any idea what the macro would look like?

thx all.

Mel





.