I Get subscript out of range when tying to access worksheet

[Pam]

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

 

[Bob Phillips]

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)

 

[Don Guillett]

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