When you're working with objects (including ranges), you need to use the Set
keyword.

set isect = ...

You could also use the same thing with the J6, K3, M4 variables.

Dim J6 as Range
set j6 = me.range("J6")
set isect = intersect(target, j6)

Personally, I wouldn't use J6 as a variable name--it doesn't provide enough info
when I'm writing the code.

If J6 was the price of apples:

dim PriceOfApples as range
set priceofapples = me.range("J6")

In fact, you may want to name the cells that you're using (Insert|Name inside
excel) and then if you insert/delete rows in your worksheet, the named cell will
(should be) ok and the code won't need to change.

dim PriceOfApples as range
set priceofapples = me.range("PriceOfApples")

(I like to use the same variable name as the name used in excel.)


Dan wrote:

hi, I am trying to make a macro perform a function if a cell in the same line
meets a desired value. not sure if on the right track, the following is an
attempt. thanks.

Dim J6 As String
Dim K3 As String
Dim M4 As String
J6 = Range("J6")
K3 = Range("K3")
M4 = Range("M4")
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), M4) 'not
working: type mismatch?
'iSect = Application.Intersect(Range(Target.Address), "A1:B1")
'orig example
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
With Me.Cells(.Row, K3).Select
End With
End If
End If
End If

'J6 has CN:CN, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CN6),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$CN6),"$",""),ROW(),"")

'K3 has CW:CW, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CW3),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$CW3),"$",""),ROW(),"")

'M4 has D:D, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$D4),"$","") ,ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address ",$D4),"$",""),ROW(),"")

--

Dave Peterson