Out on the Range

[Richie(UK)]

Hi,

I'm relatively ne to VBA and am having difficulty with a routine containing defined ranges.

I have 'cut & pasted' a routine from the net for identifying which range contains the activecell. I then want a separate sub-routine to be called for each range. Within this sub-routine I want the activecell to be coloured and the other cells on the same row to be non-coloured.

The problem is that the VBE keeps telling me that the 'Range' variable isn't defined but I thought it was.

The relevant section of code is shown below. Any suggestions / comments would be gratefully received.

Option Explicit
'
' Function to determine if a cell is within a certain range
'
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function
'
' Detect change of active cell and action appropriate macro
'
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
TestInRange
'
End Sub
'
' Test whether cell is in defined range
'
Sub TestInRange()
'
Dim Q1to10 As Range, Q11to20 As Range
Dim Q21to30 As Range, Q31to40 As Range
Set Q1to10 = Range("B5", "E14")
Set Q11to20 = Range("B15", "E24")
Set Q21to30 = Range("B25", "E34")
Set Q31to40 = Range("B35", "E44")
'
If InRange(ActiveCell, Q1to10) Then
' code to handle when the active cell is within the right range
QBank1to10
End If
If InRange(ActiveCell, Q11to20) Then
QBank11to20
End If
If InRange(ActiveCell, Q21to30) Then
QBank21to30
End If
If InRange(ActiveCell, Q31to40) Then
QBank31to40
End If
End Sub
'
Sub QBank1to10()
'*************** Questions 1 to 10 ***************
'Question 1
If Target.Address = "$B$5" Then
Range("B5", "E5").Interior.ColorIndex = 0
Range("B5").Interior.ColorIndex = 8
Range("G5").Value = 1
Range("I5").Value = ""
Range("K5").Value = ""
Range("M5").Value = ""
End If
If Target.Address = "$C$5" Then
Range("B5", "E5").Interior.ColorIndex = 0
Range("C5").Interior.ColorIndex = 8
Range("G5").Value = ""
Range("I5").Value = 1
Range("K5").Value = ""
Range("M5").Value = ""
End If
If Target.Address = "$D$5" Then
Range("B5", "E5").Interior.ColorIndex = 0
Range("D5").Interior.ColorIndex = 8
Range("G5").Value = ""
Range("I5").Value = ""
Range("K5").Value = 1
Range("M5").Value = ""
End If
If Target.Address = "$E$5" Then
Range("B5", "E5").Interior.ColorIndex = 0
Range("E5").Interior.ColorIndex = 8
Range("G5").Value = ""
Range("I5").Value = ""
Range("K5").Value = ""
Range("M5").Value = 1
End If
[Then Questions 2 to 10 are similar to Q1]
End Sub

[mcleve]

Im pretty new to VBA actually very new to VBA but I think that you forgot to add Quotation marks to:
Range("G5").Value = 1
Range("I5").Value = 1
Range("K5").Value = 1
Range("M5").Value = 1
change to:
Range("G5").Value = "1"
Range("I5").Value = "1"
Range("K5").Value = "1"
Range("M5").Value = "1"
Ithink the colorindex values need quotation marks also.
hope this helps Post and let me know

[ This Message was edited by: mcleve on 2002-05-18 09:15 ]

[Richie(UK)]

Thanks for the input but that's not it. The 1 works fine on it's own, and the blank set of quotes are to clear the other cells.

The problem occurs further up the code at the 'Target.Address' line.

[Tom Schreiner]

Hi
Just guessing here...

Set Q1to10 = Range("B5", "E14")
Set Q11to20 = Range("B15", "E24")
Set Q21to30 = Range("B25", "E34")
Set Q31to40 = Range("B35", "E44")

Should be...

Set Q1to10 = Range("B5:E14")
Set Q11to20 = Range("B15:E24")
Set Q21to30 = Range("B25:E34")
Set Q31to40 = Range("B35:E44")

Tom

[Nimrod]

Hi Mcleve:
Just a little tip on the use of quotes around numbers. The quotes are only used if you want to have the number evaluated as text and not a number.

[Richie(UK)]

Hi TsTom,

Afraid that didn't do it either. Any other ideas? (The problem area is the 'Target.Address' reference)

[Tom Schreiner]

Target.Address is defined inside a procedure.
The scope of this variable is then limited to the procedure Worksheet_SelectionChange.
You will need to define a variable at the module level and assign the target range to this variable. You can then use this new variable in any procedure located within this module.
See '##################

Example:

Option Explicit 

Dim SaveTarget as Range '################## 

' Function to determine if a cell is within a certain range 

' 

Function InRange(Range1 As Range, Range2 As Range) As Boolean 

' returns True if Range1 is within Range2 

Dim InterSectRange As Range 

Set InterSectRange = Application.Intersect(Range1, Range2) 

InRange = Not InterSectRange Is Nothing 

Set InterSectRange = Nothing 

End Function 

' 

' Detect change of active cell and action appropriate macro 

' 

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 

SaveTarget = Target '#######################

TestInRange 

' 

End Sub 

' 

' Test whether cell is in defined range 

' 

Sub TestInRange() 

' 

Dim Q1to10 As Range, Q11to20 As Range 

Dim Q21to30 As Range, Q31to40 As Range 

Set Q1to10 = Range("B5", "E14") 

Set Q11to20 = Range("B15", "E24") 

Set Q21to30 = Range("B25", "E34") 

Set Q31to40 = Range("B35", "E44") 

' 

If InRange(ActiveCell, Q1to10) Then 

' code to handle when the active cell is within the right range 

QBank1to10 

End If 

If InRange(ActiveCell, Q11to20) Then 

QBank11to20 

End If 

If InRange(ActiveCell, Q21to30) Then 

QBank21to30 

End If 

If InRange(ActiveCell, Q31to40) Then 

QBank31to40 

End If 

End Sub 

' 

Sub QBank1to10() 

'*************** Questions 1 to 10 *************** 

'Question 1 

'################## 

If SaveTarget.Address = "$B$5" Then 

Range("B5", "E5").Interior.ColorIndex = 0 

Range("B5").Interior.ColorIndex = 8 

Range("G5").Value = 1 

Range("I5").Value = "" 

Range("K5").Value = "" 

Range("M5").Value = "" 

End If 

Tom

[NateO]

If

If Target.Address = "$B$5" Then

is causing difficulty try changing it to:

If Not Application.Intersect(Target, [b5]) Is Nothing Then

[Richie(UK)]

Tom,

Top Man! That did the trick. Thanks for your help.

Richie

[Nimrod]

Hi Guys:
I'm looking at the code and see alot of redundancy instead of using the power of variables... Anyway does this code do what you trying to achieve ?
If not maybe with a bit more detail I can modify this code and help ?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'make sure active cell between column B & E
If Target.Column > 1 And Target.Column < 5 And IsNumeric(Target.Value) Then
         RowNum = Target.Row
         ColNum = Target.Column
         
        ' Clear cells B-E  of color
        Range(Cells(RowNum, 2), Cells(RowNum, 5)).Interior.ColorIndex = 0
        
        ' Clear Values in G,I,K,M
        Range("G" & RowNum).Value = ""
        Range("I" & RowNum).Value = ""
        Range("K" & RowNum).Value = ""
        Range("M" & RowNum).Value = ""
        
        ' Set color in one cell
        Cells(RowNum, ColNum).Interior.ColorIndex = 8
        
        'Set offset cell value to 1
        Cells(RowNum, ColNum + 6).Value = 1
End If
End Sub