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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format incorrect if not on starting row
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" 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 - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format incorrect if not on starting row
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. -- Kevin Vaughn "Jim Cone" wrote: 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" 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format incorrect if not on starting row
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. -- Kevin Vaughn "Jim Cone" wrote: 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" 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format incorrect if not on starting row
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. -- Kevin Vaughn "Kevin Vaughn" wrote: 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 it worked 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. -- Kevin Vaughn "Jim Cone" wrote: 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 |
Reply |
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 |