I have been working on a project with Excel. Thanks to Stack Overflow users :), I have couple of worksheet_change macros. Everything works flawlessly but one of them gives run-time error '1004' in some cases.
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Column <= 25 Then
If Target.value <> "" Then
Range("A" & Target.Row & ":Y" & Target.Row).Select
Selection.borders(xlDiagonalDown).LineStyle = xlNone
Selection.borders(xlDiagonalUp).LineStyle = xlNone
With Selection.borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.borders(xlInsideHorizontal).LineStyle = xlContinuous
Application.ScreenUpdating = True
End If
End If
So everytime I enter data in a cell, macro puts borders automatically starting from Column A till Column Y. It works alright but if a cell is selected (not a single selection but double click selection into the cell), if I try to change the sheet it gives me an error with this highlighted line:
Range("A" & Target.Row & ":Y" & Target.Row).Select
I could just deselect the cell and change sheets but I was wondering if it could be fixed within the macro?
There's no need to select at all:
If Target.Column <= 25 Then
If Target.Value <> "" Then
With Range("A" & Target.Row & ":Y" & Target.Row)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
Application.ScreenUpdating = True
End If
End If
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments