I Get subscript out of range when tying to access worksheet
 

Hello:


I am using conditional formating in my VBA macro Code. When I run
the macro I get subscript out of range. In my workbook I have the
active sheet
activated. Can some one point out what I may be doing wrong.


Here is a snipet of my code:
What I am trying to do is if BA2 has a one in it I want the pattern
for cell D2 to be red. I want this to happen from D2 to n and BA2 to
n.

I Get subscript out of range when tying to access worksheet
 

Dim n As Long
n = 150
Dim Ac As String
Ac = "mySheet"

With ThisWorkbook.Worksheets(Ac).Range("D2").Resize(n, 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=RC53=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Pam" wrote in message
ups.com...
Hello:


I am using conditional formating in my VBA macro Code. When I run
the macro I get subscript out of range. In my workbook I have the
active sheet
activated. Can some one point out what I may be doing wrong.


Here is a snipet of my code:
What I am trying to do is if BA2 has a one in it I want the pattern
for cell D2 to be red. I want this to happen from D2 to n and BA2 to
n.
I am thinking the xl should take care of this.

Dim n As Long
n = 150
Dim Ac As String
Ac = "Mysheet"

ThisWorkbook.Worksheets(Ac).Range("BA2").Select
With ThisWorkbook.Worksheets(Ac).Range("D2").Resize(n, 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$BA$2=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With


I appreciate your help!
Pam

I Get subscript out of range when tying to access worksheet
 

try this idea without the need for selections.

Sub reformat()
n = 1 '????
Set ac = Sheets("sheet11").Range("a2") '.Resize(n, 1)
With ac
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$BA$2=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With

End Sub


--
Don Guillett
SalesAid Software

"Pam" wrote in message
ups.com...
Hello:


I am using conditional formating in my VBA macro Code. When I run
the macro I get subscript out of range. In my workbook I have the
active sheet
activated. Can some one point out what I may be doing wrong.


Here is a snipet of my code:
What I am trying to do is if BA2 has a one in it I want the pattern
for cell D2 to be red. I want this to happen from D2 to n and BA2 to
n.
I am thinking the xl should take care of this.

Dim n As Long
n = 150
Dim Ac As String
Ac = "Mysheet"

ThisWorkbook.Worksheets(Ac).Range("BA2").Select
With ThisWorkbook.Worksheets(Ac).Range("D2").Resize(n, 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$BA$2=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With


I appreciate your help!
Pam