I try to use a cell range reference defined in one sub that call another sub
to change the border color of the cells in the the range. I get no errors,
but no border is painted.

Please explain why this doesn't work!

If I use the myRange.Select, I receive the error message: "Select Method of
Range class failed". How do I reference myRange in order to make it selected?
Should myRange be declared in the sub declaration, and not as a global
variable?

Regards

Frank Krogh

________________________

Private Sub MySub
With Worksheets("Sheet00")
Set myRange = .Range(.Cells( 27,7), .Cells( 27,8))
PaintBorder
end sub

Private Sub PaintBorder
With Worksheets("Sheet00")
myRange = Selection
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub

The module has this global range definition:
Dim myRange as Range

Change ur code"
myRange = Selection to myRange.Select

See updated code:

Dim myRange As Range
Private Sub MySub()
With Worksheets(1)
Set myRange = .Range(.Cells(27, 7), .Cells(27, 8))
PaintBorder
End With
End Sub

Private Sub PaintBorder()
With Worksheets(1)
myRange.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub



"Frank" wrote:

I try to use a cell range reference defined in one sub that call another sub
to change the border color of the cells in the the range. I get no errors,
but no border is painted.

Please explain why this doesn't work!

If I use the myRange.Select, I receive the error message: "Select Method of
Range class failed". How do I reference myRange in order to make it selected?
Should myRange be declared in the sub declaration, and not as a global
variable?

Regards

Frank Krogh

________________________

Private Sub MySub
With Worksheets("Sheet00")
Set myRange = .Range(.Cells( 27,7), .Cells( 27,8))
PaintBorder
end sub

Private Sub PaintBorder
With Worksheets("Sheet00")
myRange = Selection
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub

The module has this global range definition:
Dim myRange as Range

Thank you. I tried this also, but the range was not transferred.
So I tried Worksheets("Sheet00").Activate before myRange.Select, and then
it suddenly worked!


"Muhammed Rafeek M" wrote:

Change ur code"
myRange = Selection to myRange.Select

See updated code:

Dim myRange As Range
Private Sub MySub()
With Worksheets(1)
Set myRange = .Range(.Cells(27, 7), .Cells(27, 8))
PaintBorder
End With
End Sub

Private Sub PaintBorder()
With Worksheets(1)
myRange.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub



"Frank" wrote:

I try to use a cell range reference defined in one sub that call another sub
to change the border color of the cells in the the range. I get no errors,
but no border is painted.

Please explain why this doesn't work!

If I use the myRange.Select, I receive the error message: "Select Method of
Range class failed". How do I reference myRange in order to make it selected?
Should myRange be declared in the sub declaration, and not as a global
variable?

Regards

Frank Krogh

________________________

Private Sub MySub
With Worksheets("Sheet00")
Set myRange = .Range(.Cells( 27,7), .Cells( 27,8))
PaintBorder
end sub

Private Sub PaintBorder
With Worksheets("Sheet00")
myRange = Selection
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub

The module has this global range definition:
Dim myRange as Range