Sheri

try something like this in the worksheet class module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Select Case Int(Target.Value)
Case 1: Target.Interior.ColorIndex = 3 'red
Case 2: Target.Interior.ColorIndex = 36 'amber/yellow
Case 3: Target.Interior.ColorIndex = 27 'amber/dark yellow
Case 4: Target.Interior.ColorIndex = 4 'green
Case 5: Target.Interior.ColorIndex = 10 'dark green
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

Regards

Trevor


"SP" wrote in message
...
I have a question from a user. They would like the cells to change colors
based on the value. The conditional formatting will not work, since they
have more than 3 conditions. Would it be better to create a VB script or
Formula. I am looking for direction to hopefully let them decided if
Excel
will continue to suit their needs.

TIA

Sheri

Trevor, that is good. The question now have, is how to I modify to work
with the user's spreadsheet. I am not to keen on VBA, I am always seeking
help from the newsgroup, it scares me :)

Thanks Much for your Assistance

Sheri

"Trevor Shuttleworth" wrote in message
...
Sheri

try something like this in the worksheet class module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Select Case Int(Target.Value)
Case 1: Target.Interior.ColorIndex = 3 'red
Case 2: Target.Interior.ColorIndex = 36 'amber/yellow
Case 3: Target.Interior.ColorIndex = 27 'amber/dark yellow
Case 4: Target.Interior.ColorIndex = 4 'green
Case 5: Target.Interior.ColorIndex = 10 'dark green
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

Regards

Trevor


"SP" wrote in message
...
I have a question from a user. They would like the cells to change
colors
based on the value. The conditional formatting will not work, since
they
have more than 3 conditions. Would it be better to create a VB script
or
Formula. I am looking for direction to hopefully let them decided if
Excel
will continue to suit their needs.

TIA

Sheri

Sheri

how do you want to modify it ? Presumably you have access to the user's
spreadsheet so copy and paste it. I'm checking the row (3) 'cos that's
something I need to do. You can record a macro where you change the
background colour to see what you need and modify the case statements for
your values. Other than that, how do you need to change it ?

If you want to state what value and what colour, I or someone in the NG
could help you.

Regards

Trevor


"SP" wrote in message
...
Trevor, that is good. The question now have, is how to I modify to work
with the user's spreadsheet. I am not to keen on VBA, I am always seeking
help from the newsgroup, it scares me :)

Thanks Much for your Assistance

Sheri

"Trevor Shuttleworth" wrote in message
...
Sheri

try something like this in the worksheet class module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Select Case Int(Target.Value)
Case 1: Target.Interior.ColorIndex = 3 'red
Case 2: Target.Interior.ColorIndex = 36 'amber/yellow
Case 3: Target.Interior.ColorIndex = 27 'amber/dark yellow
Case 4: Target.Interior.ColorIndex = 4 'green
Case 5: Target.Interior.ColorIndex = 10 'dark green
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

Regards

Trevor


"SP" wrote in message
...
I have a question from a user. They would like the cells to change
colors
based on the value. The conditional formatting will not work, since
they
have more than 3 conditions. Would it be better to create a VB script
or
Formula. I am looking for direction to hopefully let them decided if
Excel
will continue to suit their needs.

TIA

Sheri