Change Event problem

[glenness]

Hello
I was wondering if someone can tell me what’s wrong with this Change event code? I’m trying to change the colour of a range of cells C40:F50 based on the content of cell F40. Example, if the cell(F40) contains “CNX” the range of cells turns red. If the cell(F40) contain “Proposed” the range of cell turn yellow. I can’t get this to work.

Thanks in Advance

 Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Row = 1 Then Exit Sub
  If Target.Range = Range("F40") Then
       Application.EnableEvents = False
  Select Case LCase(Target.Value)
     Case "CNX"
        Target.Range("C40:F50").Interior.ColorIndex = 3
     Case "PROPOSED"
        Target.Range("C40:F50").Interior.ColorIndex = 40
     Case Else
        Target.Range("C40:F50").Interior.ColorIndex = xlColorIndexAutomatic
  End Select
  Application.EnableEvents = True
End Sub

[BigBas]

Try

 Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Row = 1 Then Exit Sub
  If Target.Address = "$F$40" Then
  Application.EnableEvents = False
  Select Case UCase(Target.Value)
     Case "CNX"
        Range("C40:F50").Interior.ColorIndex = 3
     Case "PROPOSED"
        Range("C40:F50").Interior.ColorIndex = 40
     Case Else
        Range("C40:F50").Interior.ColorIndex = xlColorIndexAutomatic
  End Select
  Application.EnableEvents = True
  End If
End Sub

[glenness]

Hi Big Bas,
Tried the new code but still no joy....It all seems to make sense but it just won't change the colour of the cells.
Thanks for trying
Glenness

[BigBas]

Glen, I tested the code, and it worked for me. Copy and paste my code exactly into your sheet code. If it still doesn't work, post a .zip of your file and we can make it work.

Note: One of the reasons your code doesn't work was because of this line:

Select Case LCase(Target.Value)

Basically, that code takes the lowercase of the value in TARGET. However, when you look at the CASES in your select case structure, you will note that all of the entries are upper case. To correct this, change that line of code to:

Select Case UCase(Target.Value)