LinkBack URL
About LinkBacksI 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
--
Kevin Vaughn
Register To Reply
Bookmarks