Changing color background in a range from a drop down

[kwitt]

How can you make a range of cells in a row, change the background color based
on the option picked from a drop down.

 

[marcus]

Hi Kwitt

Use a combobox from the Control Toolbar, add a list fill range to the
combobox and paste this code in the sheet where the combobox exits.
Finally change the variables in this example to suit your needs.

Take care

Marcus


Option Explicit
Private Sub ComboBox1_Change()
Dim x As String

x = ComboBox1.Value ' Place a value in x.
' Start the Select Case structure.
Select Case x
Case "One"
Range("A1").Interior.Color = vbGreen
Case "Two"
Range("A1").Interior.Color = vbRed
Case "Three"
Range("A1").Interior.Color = vbBlue
Case Else 'Every other possibility.
Range("A1").Interior.Color = vbYellow
End Select

End Sub

 

[kwitt]

Marcus,
Thank you!! Ok I got it to change color. In addition to the color change I
need each combobox to be associated with only 1 row and the color changes
would be for only that row. How do you do that?? I have tried but continue to
get error statements. For example cell $H$5 will contain the drop down and
Green has been choosen, so row 5 from column A - Y will than change to the
green color. And then cell $H$6 will have its own drop down and they have
choosen Yellow, so then row 6 from column A-Y will then be changed to Yellow.

 

[Tim Williams]

Use a data validation list in column H and place this code (modify to suit)
in the sheet's code module.

Tim

'*******************************************
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, clr As Long
For Each c In Target.Cells
If c.Column = 8 Then

Select Case c.Value
Case "one": clr = vbRed
Case "two": clr = vbYellow
Case "three": clr = vbBlue
Case Else: clr = vbWhite
End Select

With c.EntireRow.Cells(1).Resize(1, 25)
.Interior.Color = clr
End With

End If
Next c
End Sub
'*******************************************