VBA function to test borders

[marcinpec]

Hi,

I have a spreadseet where in column A I have values I need to test.
It looks like this:


-- removed inline image ---


What I need is a test in column B - to identify sites - in this case: Paris, Dublin, Berlin. I'm trying to write a function to be called out in column B - to identify cells which has up/down borders - to find "site names".

What I have so far is not working - any help please?

Public Function CheckIfBorders(cellTargetCell As Variant) As String

CheckIfBorders = "Site Name - NO"

If Range(cellTargetCell).Borders(xlEdgeBottom).LineStyle <> xlNone Then
If Range(cellTargetCell).Borders(xlEdgeTop).LineStyle <> xlNone Then
CheckIfBorders = "Site Name OK"
End If
End If

 

[frolicols]

Try defining the cellTargetCell as a Range instead of a Variant.

 

[marcinpec]

Hi,

Tried but getting no joy - "VALUE" error message in cell instead.

 

[frolicols]

Set it to range, then underneath you don't need to put

Range(cellTargetCell).Border

Just use:

 cellTargetCell.Border

 

[marcinpec]

Hm,.... not working.

Maybe the way I call out the function has something to do with it?

Say:
- A3 value is "Berlin" and this cell has upper and bottom border
- A4 value is "9321-0" and has no bottom border.

In cell B3 I call out the function =CheckIfBorders(A3) and get error
Same for B4: =CheckIfBorders(A4) - I get an error.

 

[frolicols]

Try this in a module:

Function CheckBorder(Target As Range) As Boolean ' Could use String

If Target.Borders(xlEdgeTop).LineStyle <> xlNone And Target.Borders(xlEdgeBottom).LineStyle <> xlBottom Then
    CheckBorder = True ' Change this to something wrapped in "" if preferred
Else: CheckBorder = False ' Change this to something wrapped in "" if preferred
End If

End Function

 

[marcinpec]

That's perfect! Many thanks!