Color Coding Cells
 

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

Color Coding Cells
 

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

Color Coding Cells
 

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