Solved: EXCEL 2003 Changing row colour based on drop-down selection - more than 3 conditions

[paulstan]

I have a drop-down list in a cell that contains 6 items (for arguments sake named Item 1, Item 2, Item 3, Item 4, Item 5, Item 6). I would like the whole row to change colour depending on what Item was selected: it would change to Blue if Item 1 were selected; Green if Item 2 selected and so on.

I am aware that I may need to have a 'Select Case' in VB to list the items along with the colour index numbers alongside, for the 6 listed Items.

Many thanks for taking your valuable time to read this and any help much appreciated.

Paul S

[ntrauger]

Yep, utilizing select case is a good approach to solving this. Assuming your drop-down is in cell A1, paste this into the code of your sheet.[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Cells(1, 1)) Is Nothing Then Exit Sub
Select Case Target
Case "Item 1"
Target.EntireRow.Interior.ColorIndex = 1
Case "Item 2"
Target.EntireRow.Interior.ColorIndex = 2
Case "Item 3"
Target.EntireRow.Interior.ColorIndex = 3
Case "Item 4"
Target.EntireRow.Interior.ColorIndex = 4
Case "Item 5"
Target.EntireRow.Interior.ColorIndex = 5
Case "Item 6"
Target.EntireRow.Interior.ColorIndex = 6
Case Else
'May or may not need this, depending on your validation rules.
End Select
End Sub
[/vba]

[paulstan]

Nate

Thanks for your reply and your help.

The code works fine at cell A1, but only for one row and it goes all the way across the row (I need it to stop at Column AE). My drop-down list will be located at Column AD.

Not sure how many rows there will be in the spreadsheet as these will be added to by the user.

Regards

Paul S

[ntrauger]

Then make these adjustments:[vba] If Intersect(Target, Columns("AD")) Is Nothing Then Exit Sub
Select Case Target
Case "Item 1"
Target.Offset(, -29).Resize(, 29).Interior.ColorIndex = 1
'...
[/vba]

[paulstan]

Nate

That's almost done it.

A problem seems to lie with the Target.Offset. At present it is set to -29; however, this doesn't colour the columns AD & AE. If I try and change the number to anything higher than 29 it causes a runtime error '1004' at the Target.Offset line.

Logic would say just move the column with the drop-down to the end of the spreadsheet (right side), but this wouldn't follow a logical progression for the user.

Regards and many thanks to date

Paul S

[ntrauger]

My mistake. Went too fast and did not read your post carefully. Offset(, -29) places the first cell of the range to be colored in column A (29 columns back from the current column AD). The Resize() method increases the size of that range. So, just bump it up a couple of columns to include AD and AE. i.e. ...Resize(, 31)...

[paulstan]

That's done it Nate.

Can't thank you enough for your help.

Many, many thanks

Paul S

[ntrauger]

I enjoy it. Don't forget to mark your thread as solved. You'll find the button under "Thread Tools" near the top of the page.