Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format incorrect if not on starting row
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 -- Kevin Vaughn |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Incorrect | Excel Worksheet Functions | |||
Number Format Incorrect | Excel Discussion (Misc queries) | |||
incorrect number format | Excel Discussion (Misc queries) | |||
day/month/year in incorrect format for date format | Excel Worksheet Functions | |||
Incorrect date format | Excel Programming |