>3 Conditions for Data Validation for a List

[Anders]

Hi All,

I'm using xl2003 and have 5 source values for a data validation list. Each
source data cell has text, a different fill color (red, yellow, green etc)
and some change between black and white text. I use the list like any other,
in hundreds of other cells. I need the conditional formatting to expand past
3 conditions. I've seen some posts on here for doing this, just not in via a
list. My values are alphabetical for this part and to maintain consistency
with how the other users who populate the sheet with data enter it, I want to
use the list function.

Thanks!
(from the newb)

 

[Bob Bridges]

I don't think I understand this completely. I've used conditional
formatting, so I understand why needing to go beyond three conditions is a
problem; that's why you want to do it in VBA instead, I expect. But I'm not
sure what you mean when you want to do it "via a list", and that you want to
use "the list function". Maybe you're saying you want your program to go
down a list of cells and for each one a) validate the value (ie it must be
one of five values) and b) set the cell's text and background colors; sure, I
get that. Is that all you mean, or is there more to it?

 

[Anders]

Bob - you're right with a+b. I want the user to click the destination cell,
see a dropdown list containing 5 (alphanumeric) values, and select one (no
other values can be entered into the cell). Once selected, the font/fill
will change based on the item selected.

 

[Bob Bridges]

I've never fooled with drop-down lists in Excel - the occasional command
button, is all - but presumably you know how to do that part. You're just
asking asking how to ....

Come to think of it, you haven't asked anything yet. What have you tried,
and what isn't working? And HOW isn't it working?

 

[Anders]

Re: drop-down - If you have to define the values a user can input into a
cell, one where they can see what the values they can choose from are, how
would you do it? I've found the easiest for me is a data->validation->allow
list->define range etc. I'm open to other ways if they're better. **Note -
I have people not too comfortable with excel using the sheets to deliver
status updates to me so ease of use is key.

Re: what have I tried. I've gotten something to work since my last post -

1. This code is what I've modified, (found in the MS help docs): it
successfully changes the fill color
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 8 Then
Select Case .Value
Case "Not Started": .Interior.ColorIndex = 2 'White
Case "Completed": .Interior.ColorIndex = 5 'Blue
Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow
Case "Significant Issues": .Interior.ColorIndex = 3 'Red
Case "On Track": .Interior.ColorIndex = 10 ' Green
End Select
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

3. I need to change the font color as well for readability. So can I add
that to this code or do i need to write a different one? I tried writing a
different one (below, doesn't work)

Private Sub FontColorChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 8 Then
Select Case .Value
Case "Not Started": .Font.ColorIndex = 1 ' Black
Case "Completed": .Font.ColorIndex = 2 'White
Case "Manageable Issues": .Font.ColorIndex = 1 'Black
Case "Significant Issues": .Font.ColorIndex = 2 'White
Case "On Track": .Font.ColorIndex = 2 'White
End Select
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

**What i'm getting with this is the font just is never changing color. If
the code is right, maybe I'm setting it up wrong in the editor - such as
where I'm placing it???

Thanks.

 

[keiji kounoike]

Just move the code changing font color part in the FontColorChange into
your first Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 8 Then
Select Case .Value
Case "Not Started":
.Interior.ColorIndex = 2 'White
.Font.ColorIndex = 1
Case "Completed":
.Interior.ColorIndex = 5 'Blue
.Font.ColorIndex = 2
Case "Manageable Issues":
.Interior.ColorIndex = 6 'Yellow
.Font.ColorIndex = 1
Case "Significant Issues":
.Interior.ColorIndex = 3 'Red
.Font.ColorIndex = 2
Case "On Track":
.Interior.ColorIndex = 10 ' Green
.Font.ColorIndex = 2
End Select
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Keiji

 

[Anders]

Thanks Keiji! Works brilliantly!

 

[keiji kounoike]

You're welcome.

Keiji