You could also do Drafters and then undo the horizontals, but the code above is sort of a generic bit of code that you could easily adapt to various situations in the future. If, indeed, you don't want to set Inside Horizontal, then something like the following would work.ĭim myBorders() As Variant, item As Variant I noticed that in your example you didn't set the Inside Horizontal. Its also easy to apply multiple effects to the border around each cell. I learned something here - I would not have even tried what Drafter did because I would have guessed that Excel would also have set the Diagonal borders with that syntax.ī. You only need a single line of code to set the border around every cell in the range: Range('A1:F20').Borders.LineStyle xlContinuous. Then to select cell F9 and do a search down the column to find the last cell with data then offset to create the second range and set the border as required then finally offset to the right again and create the third range and set the border as required then create a loop to repeat until it finds a blank column then stops. The main function for this example is a worksheet_change event handler.A. Range("C4:F4").Borders(xlEdgeBottom).Weight = xlThick Range("C4:F4").Borders(xlEdgeBottom).Weight = xlMedium Range("C4:F4").Borders(xlEdgeBottom).Weight = xlThin Selection.Borders (xlDiagonalDown).LineStylexlNone. Sub Macro1 () 'Recorded VBA Macro to colour cells. When you add interior colour in Excel the borders tend to disappear so adding them back gives the look as follows Below is the recorded code which will produce the borders alone. Range("c4:F4").Borders(xlEdgeBottom).LineStyle = xlDouble That is a complete boarder around every cell. Range("c4:F4").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDashDotDot Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDash Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDot Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlContinuous Private Sub worksheet_change( ByVal target As Range) 'exectues when the user makes changes to the sheet The code used for this example can be seen below: For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists. VBA routine to set the grid borders THROUGHOUT a range: Sub SetRangeGrid(r As Range, Optional stl 1, Optional clr 0, Optional wgt 2) With r.Borders(). The drop down list is created using data validation. Upon selecting a new value from the drop down lists, the border on the bottom edge of the cells C4:F4 is updated: In this example the user selects the line style and line weight using drop down lists in cells A2 and B2. Range("C11:E11").Borders(xlEdgeBottom).LineStyle = xlDouble
Range("C9:E9").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot Range("C7:E7").Borders(xlEdgeBottom).LineStyle = xlDash Borders(xlInsideHorizontal).LineStyle xlNone End With End Sub Sub base. Borders(xlInsideVertical).LineStyle xlNone.
Weight xlMedium End With With Selection. In fact they could look like this: VBA Code: Sub redbrdr(ByRef ac As Range) With ac With. Sub Xmark() For Each cell In Range('A10:G26') If Sheets('Sheet1').Range('C') Then With Selection.Borders(xlDiagonalDown). Range("C5:E5").Borders(xlEdgeBottom).LineStyle = xlDashDot i am very new to vba tried with bellow one but got some errors. Range("C3:E3").Borders(xlEdgeBottom).LineStyle = xlDot Range("C1:E1").Borders(xlEdgeBottom).LineStyle = xlContinuous The code above is equivalent to the code below: Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _ The borders were created using the code below: