PDA

View Full Version : [SOLVED] What's the syntax for adding colour?



Sir Babydum GBE
07-26-2005, 07:57 AM
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
07-26-2005, 08:53 AM
You don't have it all on the same line.


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

Bob Phillips
07-26-2005, 08:57 AM
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
07-26-2005, 09:17 AM
..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
07-26-2005, 09:19 AM
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
07-26-2005, 09:21 AM
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
07-26-2005, 09:28 AM
So how to I get your suggestions into the code below?

Bob Phillips
07-26-2005, 10:10 AM
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
07-26-2005, 10:57 AM
BTW Hope I didn't offend you, just trying to add some levity.

Of course you didn't offend me! :)

Thanks for your help!