bottom border every second cell in range
I'm trying to write a macro that will apply a bottom border to every
other cell in a range. the macro only needs to work for horizontal ranges, and not for vertical ones. the closest i can get is the code below, but this only applys borders to cells in odd columns. i need it to apply borders to every other cell in a selection, regardless if it is even or odd. Sub BotBorderOdd() For Each cell In Selection If Application.WorksheetFunction.Odd(cell.Column) = cell.Column Then oRange = oRange & "," & cell.Address End If Next cell oRange = Mid(oRange, 2, Len(oRange) - 1) Range(oRange).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub Thanks for your help everyone! |
bottom border every second cell in range
Try this:
Sub BotBorderOdd() Dim i as long i = 1 For Each cell In Selection If i mod 2 = 0 Then With Cell.Bordes(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If i = i + 1 Next cell End Sub Charles Uses cass calculator wrote: I'm trying to write a macro that will apply a bottom border to every other cell in a range. the macro only needs to work for horizontal ranges, and not for vertical ones. the closest i can get is the code below, but this only applys borders to cells in odd columns. i need it to apply borders to every other cell in a selection, regardless if it is even or odd. Sub BotBorderOdd() For Each cell In Selection If Application.WorksheetFunction.Odd(cell.Column) = cell.Column Then oRange = oRange & "," & cell.Address End If Next cell oRange = Mid(oRange, 2, Len(oRange) - 1) Range(oRange).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub Thanks for your help everyone! |
bottom border every second cell in range
Charles showed you how to do it for even columns instead of ODD.
If you want to do it relative to the selection, Sub BotBorderOdd() Dim lFlag As Long lFlag = Selection(1).Column Mod 2 For Each cell In Selection If cell.Column Mod 2 < lFlag Then oRange = oRange & "," & cell.Address With cell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If Next End Sub underlines the 2nd cell and each subsequent cell. if you want to start with the first and each second cell from there, change < to = in the conditional clause. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Try this: Sub BotBorderOdd() Dim i as long i = 1 For Each cell In Selection If i mod 2 = 0 Then With Cell.Bordes(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If i = i + 1 Next cell End Sub Charles Uses cass calculator wrote: I'm trying to write a macro that will apply a bottom border to every other cell in a range. the macro only needs to work for horizontal ranges, and not for vertical ones. the closest i can get is the code below, but this only applys borders to cells in odd columns. i need it to apply borders to every other cell in a selection, regardless if it is even or odd. Sub BotBorderOdd() For Each cell In Selection If Application.WorksheetFunction.Odd(cell.Column) = cell.Column Then oRange = oRange & "," & cell.Address End If Next cell oRange = Mid(oRange, 2, Len(oRange) - 1) Range(oRange).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub Thanks for your help everyone! |
bottom border every second cell in range
You guys are so awesome. Thank you very much!
Tom Ogilvy wrote: Charles showed you how to do it for even columns instead of ODD. If you want to do it relative to the selection, Sub BotBorderOdd() Dim lFlag As Long lFlag = Selection(1).Column Mod 2 For Each cell In Selection If cell.Column Mod 2 < lFlag Then oRange = oRange & "," & cell.Address With cell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If Next End Sub underlines the 2nd cell and each subsequent cell. if you want to start with the first and each second cell from there, change < to = in the conditional clause. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Try this: Sub BotBorderOdd() Dim i as long i = 1 For Each cell In Selection If i mod 2 = 0 Then With Cell.Bordes(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If i = i + 1 Next cell End Sub Charles Uses cass calculator wrote: I'm trying to write a macro that will apply a bottom border to every other cell in a range. the macro only needs to work for horizontal ranges, and not for vertical ones. the closest i can get is the code below, but this only applys borders to cells in odd columns. i need it to apply borders to every other cell in a selection, regardless if it is even or odd. Sub BotBorderOdd() For Each cell In Selection If Application.WorksheetFunction.Odd(cell.Column) = cell.Column Then oRange = oRange & "," & cell.Address End If Next cell oRange = Mid(oRange, 2, Len(oRange) - 1) Range(oRange).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub Thanks for your help everyone! |
All times are GMT +1. The time now is 04:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com