What's the syntax for adding colour?

[Sir Babydum GBE]

Hi

If I try to change this:

Borders(xlEdgeLeft).LineStyle = xlDouble

to this:

Borders(xlEdgeLeft).LineStyle = xlDouble.ColorIndex = 11

I get told to crawl back under the rock from whence I came.

How does one add colour to borders if all the border making instruction is all on the same line of code - me no know

[Norie]

You don't have it all on the same line.

With Cells.Borders(xlEdgeLeft)
     .LineStyle = xlDouble
     .ColorIndex = 11
End With

[Bob Phillips]

Hi

If I try to change this:

Borders(xlEdgeLeft).LineStyle = xlDouble

to this:

Borders(xlEdgeLeft).LineStyle = xlDouble.ColorIndex = 11

I get told to crawl back under the rock from whence I came.

How does one add colour to borders if all the border making instruction is all on the same line of code - me no know

Oh DummyBabe!

Didi it not occur to thee that linestyle is a property pertaining to the line style, and that colour is another property.

Maybe this can help

With Range("B2").Borders(xlEdgeLeft)
    .LineStyle = xlDouble
    .ColorIndex = 11
End With

If you want to set a border around, you can do that all in one command

With Range("B2")
    .BorderAround xlDouble, xlThick, 11
End With

Being serious now, if you are going to wreak havoc with VBA, you should learn to wreak proper havoc. You need to understand the object browser. If you invoke the OB, F2 or the icon on the standard toolbar, you will be presented with a small dialog that gives you access to the Excel and VBA object models (and others). Type your object name in the search box, and you will then be presented with details on that object. You can peruse its properties at leisure then. It might become more obvious.

[Zack Barresse]

..If you want to set a border around, you can do that all in one command

With Range("B2")
    .BorderAround xlDouble, xlThick, 11
End With

Of course if you really only wanted one command, you wouldn't need the With statement, would you..

[Sir Babydum GBE]

Well, this is the problem: There is no "With Selection" thingimmigig in my code for me to play with. I was just trying to save my friend the bother of fixing this for me by attempting to do it myself. But the code is:

Trainers.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address).Borders(xlEdgeRight).LineStyle = xlDouble
Rooms.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address).Borders(xlEdgeRight).LineStyle = xlDouble

So TLC, when it comes to spreadsheet design I may be a bit Dummy, this I can't deny. But I also come up with ideas for very sexy spreadsheets (which Killian, for one, likes the sound of). One out of two ain't bad.

Anyhoo, thanks both for your advice, but, as suggested, I'm off to browse my objects. So i'll see you later.

[Bob Phillips]

Of course if you really only wanted one command, you wouldn't need the With statement, would you..

You might

With Worksheets("Sheet1")
    .Range(.Cells(1, 1), .Cells(10, 10)).Value = 99
End With

[Sir Babydum GBE]

So how to I get your suggestions into the code below?

[Bob Phillips]

So how to I get your suggestions into the code below?

You don't have a selection thingimmigig in there perhaps, but selection is just a range, and you do have a range. If we re-write your code somewhat,

Trainers.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address) _
    .Borders(xlEdgeRight).LineStyle = xlDouble
Rooms.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address) _
    .Borders(xlEdgeRight).LineStyle = xlDouble

the first of each pair of lines is the range, and Borders is a property of Range (the good ol' OB again). So you can easily implement with

With Trainers.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address)
    With .Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .ColorIndex = 11
    End With
End With
With Rooms.Range(Cells(7, i).Address & ":" & Cells(LastRow, i).Address)
     With .Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .ColorIndex = 11
    End With
End With

And you can improve the code. These two statements are equivalent

Trainers.Range (Cells(7, i).Address & ":" & Cells(LastRow, i).Address)

Trainers.Range(Cells(7, i), Cells(LastRow, i))

BTW Hope I didn't offend you, just trying to add some levity.

.

[Sir Babydum GBE]

BTW Hope I didn't offend you, just trying to add some levity.

Of course you didn't offend me!

Thanks for your help!