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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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



.

Quantcast