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)
--
I''ve encountered an error and I need to close for the weekend

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" wrote:
> 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.

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.
--
I''''ve encountered an error and I need to close for the weekend


"Bob Bridges" wrote:

> 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" wrote:
> > 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.

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" wrote:
> ...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.

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.
--
I''''ve encountered an error and I need to close for the weekend


"Bob Bridges" wrote:

> 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" wrote:
> > ...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.

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 wrote:
> 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.

Thanks Keiji! Works brilliantly!
--
I''''ve encountered an error and I need to close for the weekend


"keiji kounoike" <"kounoike A | T Pikara" wrote:

> 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 wrote:
> > 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.
>

You're welcome.

Keiji

Anders wrote:
> Thanks Keiji! Works brilliantly!