Have row color change to one of 8 different colors based on one cell's value (I, O, C, T, L, E, X, A) "Mel" <pilgrimm[ at ]agr.gc.ca> 27.09.2006 17:25:17 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

Re: Have row color change to one of 8 different colors based on one cell's value (I, O, C, T, L, E, X, A) "PCLIVE" <pclive(RemoveThis)[ at ]cox.net> 27.09.2006 17:39:26 This should get you started. You'll need to change the ColorIndex numbers to match the colors that you want to use. If you don't know the ColorIndex numbers, try recording a macro and change a cell to each of the colors that you want. Then you can look at what was recorded to find out the index number. You can adjust the Range in the For statement as needed. 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 HTH, Paul "Mel" <pilgrimm[ at ]agr.gc.ca> wrote in message news:1159377917.749738.250690[ at ]i3g2000cwc.googlegroups.com... [Quoted Text] > 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 >

RE: Have row color change to one of 8 different colors based on one ce Gary''s Student 27.09.2006 17:47:02 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: [Quoted Text] > 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 > >