try...End If 'Not Intersect(Target, rRng) Is Nothing
Private Sub Worksheet_Change(ByVal Target As Range)
Dim eRng As Range, rRng As Range
Set rRng = Sheets("Bump In - Sea").Range("E5:E51")
Set eRng = Sheets("Bump In - Sea").Range("C5:C51")
If Not Intersect(Target, eRng) Is Nothing Then 'check for range
Select Case Target.Value
Case = "Agency" 'check if yes
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = 12
Case = "Company" 'check if no
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = 6
Case Else
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = xlNone
End Select 'Case Target.Value
End If 'Not Intersect(Target, eRng) Is Nothing
.................................................................
If Not Intersect(Target, rRng) Is Nothing Then 'check for range
If Target.Value = "Special" Then 'check if yes
Target.Offset(, -4).Resize(, 11).Interior.ColorIndex = 13
Else
Target.Offset(, -4).Resize(, 11).Interior.ColorIndex = xlNone
End If 'Target.Value = "Special"
End Sub
Hi Garry
I just came across another issue which may force me to change from Worksheet Change to Selection Change.
I use a CmdBtn to .Show a CalendarPicker which works as expected, but when It passes the date to a cell.Range(G2) I get Error 9 Subscript Out of Range.
I find it somewhat frustrating given the Target.Intercept Address Range is Columns C & E, which is nothing to do with Column G
Any thoughts please.
Cheers
Mark.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 43:06:24 |
Calls: | 6,648 |
Files: | 12,193 |
Messages: | 5,329,635 |