Changing the border color\thickness

[azizrasul]

I have the following SNIPPET of code: -

Set xla = CreateObject("Excel.Application")
Set xlw = xla.Workbooks.Open(strFilename1, ReadOnly:=False)
Set xls = xlw.Worksheets(1)

xls.Cells(lngRow, intCol).Font.Color = 0
xls.Cells(lngRow, intCol).Font.Name = "Trebuchet MS"

I am trying to change the color and thickness of particular cells. Note I haven't given all the code only the bit that's needed to solve the problem (hopefully). The values of lngRow and intCol is determined by other code. Thanks in advance.

 

[Von Pookie]

Repeat after me: The macro recorder is my friend...

Use it to find out the code it uses for changing the cell border width/color. If you go through the Format Cells dialog instead of using the toolbar button, just remember to press ok before you switch line thickness or else it will only record the last change you've selected.

(So give a cell a thin border, click ok. Go back, give the cell a thick border, click ok.)

Hope it helps,

 

[Mark O'Brien]

Here's just a quick example. This will make a thick border on the right hand side of the selected cell and the colour is yellow. I got the syntax by using the Macro Recorder (Tools|Macro|Record New Macro...) and I suggest you do the same to get the syntax for the particular border styles and colours you want. Eventually you can trim your code down to something simple like this:

Public Sub test()
  Dim oRange As Range
  
  Set oRange = Selection

  With oRange
    With .Borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .Weight = xlThick
      .ColorIndex = 19
    End With
  End With
End Sub

...obviously, you should be able to adapt this code to using something simple like:

  With xls.Cells(lngRow, intCol)
    With .Borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .Weight = xlThick
      .ColorIndex = 19
    End With
  End With

I hope this gets you started.

 

[azizrasul]

Thanks guys for the quick response.

I will look at the macro recorder. I'm new to Excel and hence didn't know about it. Thanks for pointing it out. Would the macro recorder have helped me in this problem?

 

[Mark O'Brien]

Would the macro recorder have helped me in this problem?

In your particular case I would say "yes", because it's apparent that you have some decent VBA skills. All you were lacking was the syntax for changing the border colour and thickness.

 

[Von Pookie]

Well, it would have shown you the code for the LineStyle, Weight and colorindex as was posted above...

 

[azizrasul]

OK. I get it. That's pretty useful. Thanks guys.