Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two dates in one column
I found this piece of code while searching for a way to underline
anytime there is a date change in column A, but it doesnt always underline correctly and if ran twice it incorrectly underlines rows. Where is the bug? I can't find it. Any help is appreciated. thanks! Dim RowNdx As Long Dim ColNum As Integer ColNum = Selection(1).Column Columns("A:A").Select Application.ScreenUpdating = False For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value < Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx - 1, 1).EntireRow.Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With End If Next RowNdx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two dates in one column
I waited too long, but that is good, because you can see
in the other replies, how you should change your macro, however, still stands, I think Conditional Formatting is best. You don't have and IF, Else relationship to remove the border. You can instead remove all of the bottom borders for the selection at the start of the macro. You do NOT want to process every cell in Column A read about use of SpecialCells and/or UsedRange on http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/slowresp.htm As you have it your macro would probably take about 3 minutes to run even for only twenty cells with test data. I think it would be a lot easier to use Conditional Formatting and then you would not have to run a macro each time you change your data, just before you want to look at it or print it. see Color Grouping with alternating colors (#grouping) http://www.mvps.org/dmcritchie/excel...t.htm#grouping of course that is what you need, you should read the page from the top if you are not familiar with Conditional Formatting. The change will be automatic, you will not have to run a macro and you won't see a slow down because of use of C.F. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Koveras" wrote in message ps.com... I found this piece of code while searching for a way to underline anytime there is a date change in column A, but it doesnt always underline correctly and if ran twice it incorrectly underlines rows. Where is the bug? I can't find it. Any help is appreciated. thanks! Dim RowNdx As Long Dim ColNum As Integer ColNum = Selection(1).Column Columns("A:A").Select Application.ScreenUpdating = False For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value < Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx - 1, 1).EntireRow.Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With End If Next RowNdx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to do multiple IF checks with dates AND text | Excel Worksheet Functions | |||
Formula help for counting,with a column of dates and a column of n | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Comparing Cells and Displaying Data | Excel Worksheet Functions |