Colours

S

Sam

All i want to know is say you have a drop down list with
numbers 1, 2 and 3 then when you select 1 it makes the
whole row blue, select 2 it makes the whole row green,
select 3 it makes the whole row red.
I know how to make lists etc but not how to make it
automaticaly change the row colour.

Please Help

Sam
 
B

Bob Flanagan

One approach is to put the following in the combobox change event code
(reached by double clicking on the combo box:

Private Sub ComboBox1_Change()
Dim iColor As Integer
Select Case Me.ComboBox1.value
Case 1: iColor = 5
Case 2: iColor = 4
Case 3: iColor = 3
End Select
If Me.ComboBox1.ListIndex >= 0 Then
ActiveCell.EntireRow.Interior.ColorIndex = iColor
End If
End Sub

The values of the colors were obtained by using the macro recorded.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
K

Kelly

-----Original Message-----
All i want to know is say you have a drop down list with
numbers 1, 2 and 3 then when you select 1 it makes the
whole row blue, select 2 it makes the whole row green,
select 3 it makes the whole row red.
I know how to make lists etc but not how to make it
automaticaly change the row colour.

Please Help

Sam
.
Another method for a non VB user might be...
Click to expand...

Use conditional formating.

Example if the drop down was in A then select the row that
you want to formatt and then click Format/Conditional
Formatting Then chnage the condition 1 to formula and type
this formula in "=A1=1" then use the format button to
select the format you want... in thios case "red"

Then go on top condition 2 and again select formula as the
condition and type this formula "=A1=2" then use the
format button to determine the format to display when A1
is equal to 2 and so on.

HTH

Kelly

O ya I am using Excel 2000, not sure if it works on all
versions.
 
Top