error with "with statement"
 

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

error with "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

"active_x" skrev i meddelandet
...
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 ")

error with "with statement"
 

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

error with "with statement"
 

:)

error with "with statement"
 

Yes, and

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

Anders

"active_x" skrev i meddelandet
...
Workbooks("db") -- Workbooks("db.xls").