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

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).Weight = xlThick Then

Regards

Stephen Bullen
Microsoft MVP - Excel

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

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).Weight = xlThick Then
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
>
> Professional Excel Development
> The most advanced Excel VBA book available
> www.oaltd.co.uk/ProExcelDev
>
>
>

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

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
>
>