Sorry but it is impossible to keep up with you, you are moving the goalposts
continually. First it was event code, then merged cells, then it is at the
end of a large macro.

You have lost me.

--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Here is my latest attempt in solving this issue. The code is at the end
of a
very large macro

' The next line is the declaration at the start of the macro
Const WS_RANGE = "O5:O43"

' Code at the end of Macro ( Please remember I have 12 sheets, R1 thru
R12
that this has to work on.)

Worksheets(R12).Select
Range(WS_RANGE).Select
Selection.Interior

Select Case Range(WS_RANGE).Value
Case ""
Range(WS_RANGE).ColorIndex = xlNone
Case 1
Range(WS_RANGE).ColorIndex = 3
Case 2
Range(WS_RANGE).ColorIndex = 2
Case 3
Range(WS_RANGE).ColorIndex = 41
Case 4
Range(WS_RANGE).ColorIndex = 6
Case 5
Range(WS_RANGE).ColorIndex = 50
Case 6
Range(WS_RANGE).ColorIndex = 1
Case 7
Range(WS_RANGE).ColorIndex = 46
Case 8
Range(WS_RANGE).ColorIndex = 7
Case 9
Range(WS_RANGE).ColorIndex = 42
Case 10
Range(WS_RANGE).ColorIndex = 13
Case 11
Range(WS_RANGE).ColorIndex = 48
Case 12
Range(WS_RANGE).ColorIndex = 4
End Select

Worksheets(R12).Select
Range(WS_RANGE).Select
Selection.Font

Select Case Range(WS_RANGE).Value
Case ""
Range(WS_RANGE).ColorIndex = xlNone
Case 1
Range(WS_RANGE).ColorIndex = 2
Case 2
Range(WS_RANGE).ColorIndex = 1
Case 3
Range(WS_RANGE).ColorIndex = 2
Case 4
Range(WS_RANGE).ColorIndex = 1
Case 5
Range(WS_RANGE).ColorIndex = 6
Case 6
Range(WS_RANGE).ColorIndex = 6
Case 7
Range(WS_RANGE).ColorIndex = 1
Case 8
Range(WS_RANGE).ColorIndex = 1
Case 9
Range(WS_RANGE).ColorIndex = 1
Case 10
Range(WS_RANGE).ColorIndex = 2
Case 11
Range(WS_RANGE).ColorIndex = 3
Case 12
Range(WS_RANGE).ColorIndex = 1
End Select


Application.ScreenUpdating = True
Application.Cursor = xlDefault
On Error GoTo 0
End Sub




"Shu of AZ" wrote:

Thanks Shane and Bob, the code works fine except it does not follow what
the
range is saying, the only cell that changes color is O5 and none of the
others react. Below is the final code. Any corrections would be
appreciated.

NOTE: I entered additional code to change the font colors as well.


Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "O5:O43"

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case "": .ColorIndex = xlNone
Case 1: .ColorIndex = 3
Case 2: .ColorIndex = 2
Case 3: .ColorIndex = 41
Case 4: .ColorIndex = 6
Case 5: .ColorIndex = 50
Case 6: .ColorIndex = 1
Case 7: .ColorIndex = 46
Case 8: .ColorIndex = 7
Case 9: .ColorIndex = 42
Case 10: .ColorIndex = 13
Case 11: .ColorIndex = 48
Case 12: .ColorIndex = 4
End Select
End With
End If

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Font
Select Case Target
Case "": .ColorIndex = xlNone
Case 1: .ColorIndex = 2
Case 2: .ColorIndex = 1
Case 3: .ColorIndex = 2
Case 4: .ColorIndex = 1
Case 5: .ColorIndex = 6
Case 6: .ColorIndex = 6
Case 7: .ColorIndex = 1
Case 8: .ColorIndex = 1
Case 9: .ColorIndex = 1
Case 10: .ColorIndex = 2
Case 11: .ColorIndex = 3
Case 12: .ColorIndex = 1

End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub