Application Intersect

[paulstan]

Hi

I have the following code (which works perfectly - almost). My Case Select is based around Coulmn AD; however, my data starts at row 8 of the Column, with the previous 7 rows being taken up with headers etc. My problem is that if someone types something into my header at Col AD, Rows 1-7, then whichever row they type into changes to white. I would like the Select Case to start from Row 8 and ignore all prior rows.

Is there a simple change that I need to do to my code?

Regards and thanks for looking.

Paul S

Private Sub Worksheet_Change(ByVal Target As Range)
     If Application.Intersect(Target, Columns("AD")) Is Nothing Then Exit Sub
Select Case Target
    Case "Cleared : no problem"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 35
    Case "Cleared : unable to check"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 38
    Case "Cleared : errors"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 7
    Case "Not Cleared : information requested"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 8
    Case "Not Cleared : claim not processed"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 9
    Case "Not Cleared : not actioned"
        Target.Offset(, -29).Resize(, 31).Interior.ColorIndex = 10
    Case Else
       ' Change Col header title first, then uncheck line below
        Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub

 

[VoG]

Try

If Application.Intersect(Target, Columns("AD")) Is Nothing Or Target.Row < 8 Then Exit Sub

 

[Jonmo1]

Try changing this
If Application.Intersect(Target, Columns("AD")) Is Nothing Then Exit Sub
to
If Application.Intersect(Target, Range("AD8:AD" & Rows.Count)) Is Nothing Then Exit Sub

Hope that helps.

 

[paulstan]

Quick response folks!!

Both of those suggestions work.

Thanks very much for your most valuable time.

Regards

Paul S