Activecell Reference in Macro
 

Using the Macro Recorder my "Mod(Subtotal, Line below hardcoded
$A$5:$A5
I need to change this line to reflect what I've determined to be the
cell
In my line 4 which is R.Cells(1).Select

Can someone assist?
Tks in Advance
Jim May


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/9/2006 by Jim May
'
Dim R As Range
Set R = ActiveSheet.AutoFilter.Range
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1)
R.Cells(1).Select
R.FormatConditions.Delete
R.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUBTOTAL(3,$A$5:$A5),2)=1"
R.FormatConditions(1).Interior.ColorIndex = 15
End Sub

Activecell Reference in Macro
 

"=MOD(SUBTOTAL(3," & selection.address & "),2)=1"

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Jim May" wrote:

Using the Macro Recorder my "Mod(Subtotal, Line below hardcoded
$A$5:$A5
I need to change this line to reflect what I've determined to be the
cell
In my line 4 which is R.Cells(1).Select

Can someone assist?
Tks in Advance
Jim May


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/9/2006 by Jim May
'
Dim R As Range
Set R = ActiveSheet.AutoFilter.Range
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1)
R.Cells(1).Select
R.FormatConditions.Delete
R.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUBTOTAL(3,$A$5:$A5),2)=1"
R.FormatConditions(1).Interior.ColorIndex = 15
End Sub

Activecell Reference in Macro
 

Thanks Gary;
Works great..


"Gary Brown" wrote:

"=MOD(SUBTOTAL(3," & selection.address & "),2)=1"

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Jim May" wrote:

Using the Macro Recorder my "Mod(Subtotal, Line below hardcoded
$A$5:$A5
I need to change this line to reflect what I've determined to be the
cell
In my line 4 which is R.Cells(1).Select

Can someone assist?
Tks in Advance
Jim May


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/9/2006 by Jim May
'
Dim R As Range
Set R = ActiveSheet.AutoFilter.Range
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1)
R.Cells(1).Select
R.FormatConditions.Delete
R.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUBTOTAL(3,$A$5:$A5),2)=1"
R.FormatConditions(1).Interior.ColorIndex = 15
End Sub