Old Macros have an evidently simple problem
Posted by Sukant Hajra on November 07, 2001 6:41 AM
Okay,
Before, I was banking on "Rows" and "Columns" being collections of Objects called "Row" and "Colomn" but I think that changed in Excel 2000. Well. . . I can't say really. All I know is that the simple macro used to work in an earlier version of Excel, but it doesn't work in 2000.
I really think the problem is with the way I'm using for each, and Excel is set up not to mandate variable declaration.
Please respond with any help to my E-mail, Sukant.Hajra@motorola.com.
Thanks,
Sukant
Sub AlternateOnChange()
Dim Toggle As Boolean
Dim iSect As Range
Set iSect = Application.Intersect(ActiveSheet.UsedRange, Selection)
Toggle = True
For Each r In iSect.Rows
If Toggle = True Then
r.Interior.Color = RGB(210, 210, 210)
Else
r.Interior.Color = RGB(255, 255, 200)
End If
If r.Cells(1, 1) <> r.Cells(2, 1) Then
Toggle = Not Toggle
End If
Next r
End Sub
Posted by Anon on November 07, 2001 3:08 PM
The code works for me and I don't see why it shouldn't (unless the selection is entirely outside of the sheet's used range).
In what way is it not working? Are you getting an error mesaage?
PS :-
To cover the possibility of the selection being outside of the used range :-
Sub AlternateOnChange()
Dim Toggle As Boolean
Dim iSect As Range
Set iSect = Application.Intersect(ActiveSheet.UsedRange, Selection)
If iSect Is Nothing Then
MsgBox "The selection is not within the used range"
Exit Sub
End If
Toggle = True
For Each r In iSect.Rows
If Toggle = True Then
r.Interior.Color = RGB(210, 210, 210)
Else
r.Interior.Color = RGB(255, 255, 200)
End If
If r.Cells(1, 1) <> r.Cells(2, 1) Then
Toggle = Not Toggle
End If
Next r
End Sub
