Question from a rookie VBA user

[Peskarik]

Hello,
I am a total rookie when it comes to VBA, used it for 2 days. I am reading a book and experimenting, and got into trouble. Cannot understand what I am doing wrong. :dunno



background and question:
I have an open workbook with 2 worksheets: "Shading" and "main". I want to shade every second row in Shading as long as there are values in the first column (I have values 1 thru 25 in column A). The code for this I have from the VBA bible, but I wanted to play a little by running the procedure while "main" is selected. And it does not work. But if I select "Shading" and run the procedure - it works. Could someone please explain why it does not work? :banghead: Is "With" statement incorrectly specified? Thanks in advance! :friends:

Module1 code:

Option Explicit

Sub ShadeEverySecondRow()
With ThisWorkbook.Worksheets("Shading")
.Range("A2").EntireRow.Select
Do While ActiveCell.Value <> ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End With
End Sub

[Bob Phillips]

The problem is because activecell may not be on Shading.

Try this



Sub ShadeEverySecondRow()
Dim LastRow As Long
Dim i As Long

With ThisWorkbook.Worksheets("Shading")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow Step 2
.Rows(i).Interior.ColorIndex = 15
Next i
End With
End Sub

[Peskarik]

Aha! I think I understand what you mean. When I select "main" there is one cell selected, and this cell in main is the ActiveCell. Have I understood correctly?

[Bob Phillips]

Exactly. So your code was looking athe 'Main' sheet, not the 'Shading' sheet. One of the big dangers of using Selection and Activecell, which you will note I studiously avoided.

Actually, your code fails for me if I don't have Shading Active, but no matter, it is a poor approach anyways.

[Peskarik]

Thanks, xld!

[Bob Phillips]

BTW I forgot to mention that you can ave many cells selected, and the first selected is the activecell. Selection and Activecell are often the same, but not necessarily. Selection always contains Activecell (unless an ob ject is selected, but that is another story).

[Peskarik]

Merci!