Format rows

F

FDA

I am workin on a sheet that I would like to do the impossible.

I am creating a drop down list of topics. I would like to assign a color to
each topic and have excell highlight the row depending on the topic. Is this
truely imposible?
 
D

Don Guillett

You should be able to do this using a select case macro
case is "joe":mycolor=4
etc
 
G

Gord Dibben

Not imp;ossible at all.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
On Error GoTo endit
Application.EnableEvents = False
vals = Array("Item1", "Item2", "Item3", "Item4", "Item5")
nums = Array(8, 9, 6, 3, 7, 4)
icolor = 0
For i = LBound(vals) To UBound(vals)
If r.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
r.EntireRow.Interior.ColorIndex = icolor
End If
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Assumes A1 has the DV list dropdown.

Edit Item names and colors to suit.


Gord Dibben MS Excel MVP
 
F

FDA

First, thanks for letting me know that it isn't impossible...This is my first
venture behind the scene's of Excel, so, like many virgins, I am nervous...

I tried the copy and paste, but didn't get too far. Perhaps more info will
help:

Column A (starting with row 3 and continueing down to row 89) will have the
Theme drop down list. The theme's include Food Protection Plan (green),
Medical Product Safety (pink), Personalized Medicine (blue), Expanding the
Horizons of Medicine (purple), Food and Drug Safety (orange) and
Strengthening FDA (yellow). Each has a color assigned to it (see parenthisis
above). I would like the user to be able to select the Theme from the drop
down list and have that row "highlighted" with the applicable color. Then,
they could go to the next row and select another theme that would be
highlighted with the new (applicable) color.

Still possible? I have the drop down created, just not sure about the
specific coding for it.
 
G

Gord Dibben

"didn't get too far" is hard to troubleshoot.

Copy and paste to a sheet module as instructed is pretty straight-forward so you
must have been able to get that far.

This revised code including tailored edits will serve you better with a
multi-cell range of A3:A89

Delete the first set of code from your sheet module.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Range
Set r = Range("A3:A89")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo endit
Application.EnableEvents = False
vals = Array("Food Protection Plan", "Medical Product Safety", _
"Personalized Medicine", "Expanding the Horizons of Medicine", _
"Food and Drug Safety", "Strengthening FDA")
nums = Array(8, 9, 6, 3, 7, 4)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord
 
F

FDA

Ok...this has been working like a champ, but now they want to change it.
Instead of having the entire row changed to a new color, they just want the
first colum (A). How does one make that happen?
 
S

sbitaxi

Conditional formatting should do it.

Select column A, and from the format menu select conditional
formatting.
Use "Cell Value is equal to" and click add for additional criteria.
Use the Pattern tab in the formats to select a solid colour to match.



Steve
 
G

Gord Dibben

OK if OP has Excel 2007 which has more than 3 conditions allowed.

Note he has 6 conditions.


Gord
 
Top