Conditional format incorrect if not on starting row

[Guest]

I have a program that is mostly working but I have discovered, more or less
by trial and error that if my cursor is not on the row in which the program
will start adding data, then the conditional format formula my procedure
builds will use the incorrect row. I know that the variable I am using
represents the correct row for the CF as I have put a break point on that
line, but still the CF does not use the variables content unless I make sure
my cursor is on the row that the variable is equal to.
Here is the line where I have my breakpoint and below that is the complete
section where the problem is occurring. It doesn't seem like I should have
to have my cursor in any particular row, and if anyone else ever ends up
using this, I know that is going to be too hard to explain why they need to
do that.

..FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"

Say I start out on row 7 and the procedure is bringing in 4 rows worth of
data from another spreadsheet. When I run this the first time,
ltrackstartrow = 7. CF that is created is perfect. I want to test it again,
so I run the macro again (when I first noticed the problem) without moving my
cursor. When I debug the program during the 2nd run, ltrackstartrow is 11
which is correct, but when I look at the CF, it is referencing row 15. While
experimenting, I had my cursor on, for instance row 4 before running it the
first time, and my CF was wrong. And finally, just a while ago, I ran the
procedure 3 times in a row, each time making sure my cursor was in the row
that would be the same as ltrackstartrow. Every time the CF that was built
was correct.

I hope someone can offer a solution so that the CF will be correctly built
regardless of where my cursor is when I run this. If you need to see the
entire procedure, let me know and I will post it.
Thanks.

With wsTracking
.Range(.Cells(lTrackStartRow, iTrackStartCol),
..Cells(lTrackFinalRow, 21)).Style = "MyInput"
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 15))
.FormulaR1C1 = "=if(weekday(rc[-1])>3,rc[-1]+5,rc[-1]+3)"
.Style = "MyFormula"
.NumberFormat = "m/d/yyyy"
End With
' The above takes care of everything except 2 columns within the
range that
' need a slightly different formula
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 12))
.FormulaR1C1 = "=if(weekday(rc[-1])=6,rc[-1]+3,rc[-1]+1)"
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 14), _
.Cells(lTrackFinalRow, iTrackStartCol + 14))
.FormulaR1C1 = "=if(weekday(rc[-1])=6,rc[-1]+3,rc[-1]+1)"
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol), _
.Cells(lTrackFinalRow, iTrackStartCol + 8))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 46
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$B" & lTrackStartRow & ")"
.FormatConditions(2).Interior.ColorIndex = 6
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 12))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",not($C" _
& lTrackStartRow & "),$b" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 6
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 14), _
.Cells(lTrackFinalRow, iTrackStartCol + 14))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End With

 

[Jim Cone]

Kevin,

John Walkenbach has something to say on the subject here...
http://j-walk.com/ss/excel/odd/odd07.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Kevin Vaughn"
<[email protected]>
wrote in message
I have a program that is mostly working but I have discovered, more or less
by trial and error that if my cursor is not on the row in which the program
will start adding data, then the conditional format formula my procedure
builds will use the incorrect row. I know that the variable I am using
represents the correct row for the CF as I have put a break point on that
line, but still the CF does not use the variables content unless I make sure
my cursor is on the row that the variable is equal to.
Here is the line where I have my breakpoint and below that is the complete
section where the problem is occurring. It doesn't seem like I should have
to have my cursor in any particular row, and if anyone else ever ends up
using this, I know that is going to be too hard to explain why they need to
do that.
- snip -

 

[Guest]

Thanks. I'll read that. In the meantime I did come up with what appears to
be a workable solution. I tried it this morning and in three subsequent
tests. I don't know if it is what John's site discusses but I activated a
cell that was in the correct row and my CF is correct.

 

[Guest]

Yes that link was helpful. Now that I just read it, I believe I read it
before (but inconveniently managed to forget it when I needed it.) I don't
think I read it in one of his books so I must have read it on that site.

Thanks again.

 

[Guest]

Now that I re-read this, it doesn't appear clear from my previous post that I
activated the row in my macro. Just for the sake of completeness, the line I
added was:

wsTracking.Cells(lTrackStartRow, iTrackStartCol).Activate

Also I just now noticed a typo. I fixed it now but in the sentence that
said in three subsequent tests I didn't include the words it worked.