Checking for the border style of a range
 

Good morning all!

I recently wrote some event code that put a thick border around three cells
whenever they were clicked. This was to indicate the status of a job, whether
it was red, amber or green.
I now want to check which of the three cells has the outline, to transfer a
status of either red, amber or green to a record in a database.

I started with:

if activesheet.range("IssueRed").BorderAround Weight:=xlThick then

but this doesn't seem to be quite right.

Can anyone out there enlighten me as to the syntax I should be using?

Thanks in advance and have a nice weekend!

Pete

Checking for the border style of a range
 

Hello,

try activesheet.range("IssueRed").Borders.Weight = xlThick instead. The
BorderAround Method seems only suitable for setting the border
Properties, not for reading them.
As far as I understand it, using the Borders property in the way I
mentioned above, will only work, if the setting are the same for all
Borders of the cell, otherwise you have to pick a specific border with
one of the following constants:
xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight,
xlEdgeTop, xlInsideHorizontal oder xlInsideVertical

E.g. activesheet.range("IssueRed").Borders(xlEdgeBottom ).Weight =
xlThick will check for the bottom border.

Andreas

Checking for the border style of a range
 

Hi Peter,

if activesheet.range("IssueRed").BorderAround Weight:=xlThick then

but this doesn't seem to be quite right.

Can anyone out there enlighten me as to the syntax I should be using?

You can't check for the outline as a whole, only individual borders:

If ActiveSheet.Range("IssueRed").Borders(xlTop).Weigh t = xlThick Then

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Checking for the border style of a range
 

Stephen,

Spot on. Thank you very much!

Have a green tick and a good weekend!

Pete



"Stephen Bullen" wrote:

Hi Peter,

if activesheet.range("IssueRed").BorderAround Weight:=xlThick then

but this doesn't seem to be quite right.

Can anyone out there enlighten me as to the syntax I should be using?

You can't check for the outline as a whole, only individual borders:

If ActiveSheet.Range("IssueRed").Borders(xlTop).Weigh t = xlThick Then

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Checking for the border style of a range
 

Andreas,

Thank you for this! :-)

Regards

Pete

"Andreas Maistmann" wrote:

Hello,

try activesheet.range("IssueRed").Borders.Weight = xlThick instead. The
BorderAround Method seems only suitable for setting the border
Properties, not for reading them.
As far as I understand it, using the Borders property in the way I
mentioned above, will only work, if the setting are the same for all
Borders of the cell, otherwise you have to pick a specific border with
one of the following constants:
xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight,
xlEdgeTop, xlInsideHorizontal oder xlInsideVertical

E.g. activesheet.range("IssueRed").Borders(xlEdgeBottom ).Weight =
xlThick will check for the bottom border.

Andreas