Changing Row Color

[Gs]

I would sure appreciate some assistance in row shading / colors.

I am using Excel 2003. I have data in columns A through N, with multiple
rows. Column G has a list box / drop down box, that you make a selection from
the list. There are 6 choices in the list. I am trying to make the row color
change based on what is selected in the list in column G.

For example, when apple is selected from the list, that specific row turns
red. The next row, grape is selected and the row turns green, plum = purple,
corn = yellow, etc.

Conditional formatting allows for 3 conditions, I need 6.

Please and thank you, in advance, for any and all assistance / help /
suggesions.

Gs

 

[Jacob Skaria]

Right click the sheet tab >View code and paste the below code in the code
panel...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCIndex As Integer
If Not Application.Intersect(Target, Range("G:G")) Is Nothing Then
Select Case Target.Text
Case "Apple"
intCIndex = 3
Case "Grape"
intCIndex = 4
Case "Corn"
intCIndex = 5
Case "Orange"
intCIndex = 6
End Select
If intCIndex <> 0 Then
Rows(Target.Row).Interior.ColorIndex = intCIndex
Else
Rows(Target.Row).Interior.ColorIndex = 0
End If
End If
End Sub

Refer color index for more colors
1 - Black
2 - White
3 - Red
4 - Green
5 - Blue
6 - Yellow
7 - Magenta
8 - Cyan

If this post helps click Yes

 

[Gs]

Thank you for responding so quickly. I did as you suggested, but it didn't
work for me. Can you help more? Do I have to do something to activate it?
I copied and pasted it in the panel and then selected File/Close and Return
to Excel. And, there aren't any colors being displayed. What am I missing?
Please......

 

[Jacob Skaria]

--Set the Security level to low/medium in (Tools|Macro|Security).
--Try opening a new workbook. Right click 'Sheet1' tab>View code and paste
the code to the code module
--Get back to workbook and type Apple in Col G (any row)


Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCIndex As Integer
If Not Application.Intersect(Target, Range("G:G")) Is Nothing Then
Select Case Ucase(Target.Text)
Case "APPLE"
intCIndex = 3
Case "GRAPE"
intCIndex = 4
Case "CORN"
intCIndex = 5
Case "ORANGE"
intCIndex = 6
End Select
If intCIndex <> 0 Then
Rows(Target.Row).Interior.ColorIndex = intCIndex
Else
Rows(Target.Row).Interior.ColorIndex = 0
End If
End If
End Sub



If this post helps click Yes

 

[FSt1]

hi
you said you "pasted it in the panal".
did you paste it into a standard module or a sheet module? the code that
Jacob supplied is sheet code. right click the sheet tab that you want the
code to run on and from the popup, click view code. paste it there.

Regards
FSt1

 

[Gs]

Yes, it works in the new workbook. I am assuming that it has something to do
with the drop down list in my original workbook. Suggestion?

 

[Gs]

Yes, that is what I did.

hi
you said you "pasted it in the panal".
did you paste it into a standard module or a sheet module? the code that
Jacob supplied is sheet code. right click the sheet tab that you want the
code to run on and from the popup, click view code. paste it there.

Regards
FSt1

 

[Jacob Skaria]

The entries in the list must be having spaces like 'Apple '. To handle this
replace the below line in the code with

<<Select Case Ucase(Target.Text)

Select Case Ucase(Trim(Target.Text))

If this post helps click Yes

 

[Gs]

Nope, that didn't work either.

The entries in the list must be having spaces like 'Apple '. To handle this
replace the below line in the code with

<<Select Case Ucase(Target.Text)

Select Case Ucase(Trim(Target.Text))

If this post helps click Yes

 

[Jacob Skaria]

It is somthing to do with the workbook. Do you have any conditional
formatting rules? Once you are able to identify; let us know

If this post helps click Yes

 

[Jacob Skaria]

Let me know your email ID if you would wish me to look into your file

 

[YESHWANT]

Hi Gs,

u can also try the following technique

select the range u want to format
click on format - conditional format - select "formula is" and
in the next field type (column constant & row)="apple"
format as per your choice

u r at home.

click yes below, if it helps

 

[Gord Dibben]

And how would that overcome the 3 condition limit imposed by Excel 2003 when
OP has six conditions.


Gord Dibben MS Excel MVP

 

[Gs]

I tried this again this evening, and it is working! Not sure what I was
doing wrong last night. Anyway, thank you, Jacob, for the coding. I
appreciate it very much!