relating to named range in vba

[Rob Hargreaves]

Hi I have found a variation on some code and it works fine when i set the
targ values below as numbers.

I cant get it to refer to the cells which are provided as named ranges.

You can see below my efforts in the code below.

Please can someone give me the correct code to make it work

Thanks for your help.
Rob

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error

Dim icolour As Integer

Dim targ1 As Long
Dim targ2 As Long
Dim targ3 As Long
Dim targ4 As Long
Dim targ5 As Long
Dim targ6 As Long
Dim targ7 As Long
Dim targ8 As Long

targ1 = ''''
targ2 = ''''
targ3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Value
targ4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Value
targ5 = NaburnMLSSTrig3a
targ6 = NaburnMLSSTrig3b
targ7 = ''''
targ8 = ''''

'EXAMPLE FROM POST
'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Value

If Not Intersect(Target, Range("Y:AB")) Is Nothing Then
Select Case Target
Case targ1 To targ2
icolour = 45
Case targ3 To targ4
icolour = 3
Case targ5 To targ6
icolour = 45
Case targ7 To targ8
icolour = 3
Case Else
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select
Target.Interior.ColorIndex = icolour
End If

 

[Tom Ogilvy]

I am not sure what your exact problem is, but all you targ variables are
dim'd as LONG, so the only thing they can hold is a whole number. If you
want to put in a string as your code, shows, type them as string or variant
(use variant if they could hold a string or a number).

 

[Guest]

Rob,
Assuming NaburnMLSSTrig3a is a named range then:

targ5 = Range("NaburnMLSSTrig3a")

Similarly fot targ6 (and targ3/targ4 could be defined in same way).
should work.

Normal format is MyVar=Range("RangeName")

HTH

 

[Dana DeLouis]

If I understand this, 1 additional problem might be this...

If Target is blank, you want this:

Case targ7 To targ8
icolour = 3

But it will never get there because blanks are tested earlier.

Case targ1 To targ2
icolour = 45

I'm not sure what you want to do with Blanks, but here's another version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim icolour
Select Case Target
Case vbEmpty
icolour = 45 ' Or 3 ??
Case Range("NaburnMLSSTrig2a") To Range("NaburnMLSSTrig2b")
icolour = 3
Case Range("NaburnMLSSTrig3a") To Range("NaburnMLSSTrig3b")
icolour = 45
Case Else
icolour = xlColorIndexNone
End Select
Target.Interior.ColorIndex = icolour
End Sub