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

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
 
P

PCLIVE

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
 
G

Gary''s Student

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.
 
M

Mel

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
 
P

PCLIVE

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
 
M

Mel

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
 
P

PCLIVE

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
 
M

Mel

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
 
P

PCLIVE

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to filter rows data based on background color of individual cells 3
Alternate Row Coloring 7
How to have row color change based on one cell to 4 colors 1
Dynamically setting CELL COLORS based on TWO OTHER cell values 3
Formula to set cell color 3
Recognize Cell Color and Sum Data accordingly 3
Change background color of cell based on vlookup in cell 1
macro or formula to paste values from one tab to another 1

Top