Hi Team

Is there a way to incorporate 2 individual addresses.

Target, eRng works as expected, but the rRng does not.

So, after the user makes first selection, in the same row, 2 cols to the right, if they select "Special", I would like it to change the colour.

Cannot seem to get the logic right to make it happen.

TIA
Mark.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim eRng As Range: Set eRng = Sheets("Bump In - Sea").Range("C5:C51")
Dim rRng As Range: Set rRng = Sheets("Bump In - Sea").Range("E5:E51")

If Intersect(Target, eRng) Is Nothing Then Exit Sub 'check for range
If Target.Value = "Agency" Then 'check if yes
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = 12
ElseIf Target.Value = "Company" Then 'check if no
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = 6
Else
Target.Offset(, -2).Resize(, 11).Interior.ColorIndex = xlNone
End If

.................................................................
If Intersect(Target, rRng) Is Nothing Then Exit Sub '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

End Sub

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

try...

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 If 'Not Intersect(Target, eRng) Is Nothing

End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Garry

Initially, I did try at a Case Statement, although I used Case True, which did not work, hence why I went down the If/Else.

Many thanks for the assist.

Cheers
Mark.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Oops! typo...

try...

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 If 'Not Intersect(Target, rRng) Is Nothing

End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

ALWAYS show your code; - we can't imagine what it is and so not able to answer!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Garry

It was the same code you just helped me with.

No matter, I decided to build a Sub with a loop and a CmdBtn on the sheets to update, it's manual, but it works without issue.

Thanks again.
Cheers
Mark

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)