format borders of cell with VBA

[white_flag]

Hello
I have the following code that will try to put some lines around cells that have values inside:

the code is like this but I received error 438 (Object doesn't support this property or method)
How can be this fixed?

error is starting from here:

With .Borders(xlEdgeTop)

Dim Cell As Range
With Sheets(strSheetName)
    For Each Cell In Sheets(strSheetName).Range("B1:J23")
         If Cell.Value > "" Then
               With .Borders(xlEdgeTop) 
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlInsideHorizontal)
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
         End If
    Next
End With

 

[VoG]

Try

Dim Cell As Range
With Sheets(strSheetName)
    For Each Cell In .Range("B1:J23")
        With Cell
         If .Value <> "" Then
               With .Borders(xlEdgeTop)
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlInsideHorizontal)
                    .LineStyle = xlDot
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
         End If
        End With
    Next
End With

 

[white_flag]

following your code

I have error 1004 (unable to set line property of the Border class) at line:

.LineStyle = xlDot

I do not understand why it is acting like this (I have an similar code in a different worksheet and it working ok)
on this one I just ad the if statement (if cell is not empty make the borders)..anyway

 

[VoG]

Sorry, I don't know why you are getting that error. The macro recorder gives the same syntax for adding a dotted border.

 

[white_flag]

If I take this out

With .Borders(xlInsideHorizontal)
    .LineStyle = xlDot
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

it is working but not the way I like ... I have to dig