error with "with statement"

[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 ")

 

[Anders S]

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

 

[active_x]

Workbooks("db") --> Workbooks("db.xls").

 

[active_x]

 

[Anders S]

Yes, and

Interior.ColorIndex = 9 --> .Interior.ColorIndex = 9

Anders