Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
relating to named range in vba
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")).Va lue targ4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue targ5 = NaburnMLSSTrig3a targ6 = NaburnMLSSTrig3b targ7 = '''' targ8 = '''' 'EXAMPLE FROM POST 'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).V alue 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
relating to named range in vba
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). -- Regards, Tom Ogilvy "Rob Hargreaves" wrote in message ... 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")).Va lue targ4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue targ5 = NaburnMLSSTrig3a targ6 = NaburnMLSSTrig3b targ7 = '''' targ8 = '''' 'EXAMPLE FROM POST 'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).V alue 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
relating to named range in vba
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 "Rob Hargreaves" wrote: 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")).Va lue targ4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue targ5 = NaburnMLSSTrig3a targ6 = NaburnMLSSTrig3b targ7 = '''' targ8 = '''' 'EXAMPLE FROM POST 'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).V alue 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
relating to named range in vba
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 -- Dana DeLouis Win XP & Office 2003 "Rob Hargreaves" wrote in message ... 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")).Va lue targ4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue targ5 = NaburnMLSSTrig3a targ6 = NaburnMLSSTrig3b targ7 = '''' targ8 = '''' 'EXAMPLE FROM POST 'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).V alue 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question relating to Named Ranges which exist in another workbook. | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Problem with an "if" relating to a range | Excel Worksheet Functions | |||
A question relating to "for each cell_in_loop in range" | Excel Programming |