asked on

Get the last column in excel range

Dear Experts:

below macro applies alternate shading from Row 2 downwards.

Could somebody help me tweak the code as follows:

- the last column is not be hard coded (see line 11)
- the very first row is to be shaded RGB (240, 240, 240) and the font color is to be 'white' (RGB 0, 0, 0)

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub AlternateRowColors()
Dim lastRow As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

Dim lColumn As Long

lastRow = ws.Range("A1").End(xlDown).Row
'D is not to be hard coded but the last column (which is different on every worksheet) should adjust according to the used range
For Each Cell In ws.Range("A2:D" & lastRow)
    If Cell.Row Mod 2 = 1 Then
        Cell.Interior.Color = RGB(240, 240, 240)
    Else
        Cell.Interior.ColorIndex = xlNone
    End If
Next Cell

Next ws

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

Try this please:

Sub AlternateRowColors()
Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

Dim lColumn As Long

lastRow = ws.Range("A1").End(xlDown).Row
lastCol = Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Range("A1", Cells(lastCol, 1).Interior.Color = RGB(240, 240, 240)

For Each Cell In ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))
    If Cell.Row Mod 2 = 0 Then
        Cell.Interior.Color = RGB(240, 240, 240)
    Else
        Cell.Interior.ColorIndex = xlNone
    End If
Next Cell

Next ws

End Sub

Open in new window

~bp

Subodh Tiwari (Neeraj)

@Bill

Line#15 should be like below...

For Each Cell In ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))

Open in new window

Bill Prew

Corrected. That is best practice, agreed, I think in my simple test it wasn't needed but should be there for scalability.

~bp

Andreas Hermle

ASKER

Great job Subodh, exactly how I wanted it. Thank you very much.

I am afraid to tell you Bill, that your code throws an error message, Subodh corrected it. You were very close.

Anyhow, thank you very much for your great, professional and swift help.

Regards, Andreas

Subodh Tiwari (Neeraj)

You're welcome Andreas! Glad I could help.