Have row color change to one of 8 different colors based on one cell's value (I, O, C, T, L, E, X, A

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

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
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
This appears to work. I also added a Case "" in the event that you delete
one of the letters from that column, it will change the row color back to
none. The only thing with this is that for some reason when you use the
letter "i", it seems to automatically capitalize it the first time and then
any other "i"s in succession are lower case. Skip a row and enter another
"i" and it is capitalized again. I'm not sure why that's happening, but it
still seems to work nonetheless.

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 ""
Target.EntireRow.Interior.ColorIndex = 0
Case "I", "i"
Target.EntireRow.Interior.ColorIndex = 4
Case "O", "o"
Target.EntireRow.Interior.ColorIndex = 5
Case "C", "c"
Target.EntireRow.Interior.ColorIndex = 6
Case "T", "t"
Target.EntireRow.Interior.ColorIndex = 7
Case "L", "l"
Target.EntireRow.Interior.ColorIndex = 8
Case "E", "e"
Target.EntireRow.Interior.ColorIndex = 9
Case "X", "x"
Target.EntireRow.Interior.ColorIndex = 10
Case "A", "a"
Target.EntireRow.Interior.ColorIndex = 11
End Select
End Sub
 
Back
Top