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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 worksheet 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 worksheet code.
--
Gary''s Student


"Mel" wrote:

Have a spreadsheet 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





.

Relevant Pages

  • Re: How to capture Max cell value (High Water Mark)
    ... Where A1 is the cell that is changing and the HiWater function can be in any ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... I'm only using it on the same worksheet where the macro was created. ...
    (microsoft.public.excel)
  • Re: How to capture Max cell value (High Water Mark)
    ... directly addressing the changing cell. ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... "Note that it only works with the worksheet under which it was ...
    (microsoft.public.excel)
  • Info Only: Formula Freeze Module
    ... Dim rngTest As Range ... .ScreenUpdating = False ... Public Sub RecordDelete ... Dim cell As Object ...
    (microsoft.public.excel.programming)
  • Re: start programing
    ... This should open the Visual Basic Editor. ... Visual Basic automatically will create an "End Sub". ... program to set the font attributes, borders and color of the cell. ... Macro Recorder, select the cell, set Bold, heavy border outline ...
    (comp.programming)
  • Re: Have row color change to one of 8 different colors based on one ce
    ... Private Sub Worksheet_Change ... Exit Sub ... One thing I did notice is that when I type in the cell value in column ... thx again. ...
    (microsoft.public.excel.misc)