error with "with statement"

  • Thread starter Thread starter active_x
  • Start date Start date
A

active_x

Example:

1) Two excel files are opened: "db.xls" and "sys.xls"

2) The following vba codes are added to "sheet1" of "sys.xls":

-------------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row = 1 Then

With Workbooks("db").Worksheets("Sheet1").Cells(1, 1)
.Interior.ColorIndex = 9
End With

End If

End Sub

-----------------------------------------------------------------------

3) a1 is clicked

Question 1: why there is no change (cells.interior.colorindex) in
db.xls-->sheet1--> A1 ? what's wrong with the " with statement
"?

Question 2: how should the codes be re-written ? (STILL USING " with
statement ")
 
This works for me:

'---------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row = 1 Then

With Workbooks("db.xls").Worksheets("Sheet1").Cells(1, 1)
.Interior.ColorIndex = 9
End With

End If

End Sub

'----------


HTH
Anders.Silvén
 
Back
Top