Why is this only applying the format to column A instead of the selected column J

Question

Im trying to alternate the alignment of a column to make reading a little easier, but even though im specifying the column J it always applies the formatting to column A

If i create a new sheet and run this against a simple 1 column sheet, it works great and as expected. But i need it to run against column J

Sub alternateAlignLeft()
    Columns("J:J").Select
    For rowx = 2 To 1000 Step 2
    Cells(rowx, 1).HorizontalAlignment = xlLeft
    Next
End Sub


Sub alternateAlignRight()
    Columns("J:J").Select
    For rowx = 1 To 1000 Step 2
    Cells(rowx, 1).HorizontalAlignment = xlRight
    Next
End Sub

I need this code, which works great against column A, to work only for column J. What am i doing wrong?

`Cells(rowx, 1).HorizontalAlignment = xlRight` is referring directly to row rowx, column 1. Not within your selection, within your worksheet. If you want it to work in column J, change it to `Cells(rowx, 10)` (10 references the 10th column, or J) — Plutian, Sep 27 '19 at 14:13
so do i even need the Columns("J:J").Select line? seems like i dont — CubanGT, Sep 27 '19 at 14:41

riskypenguin · Answer 1 · 2019-09-27T17:54:10.270

You should generally try to avoid .Select as is described here.

Try this code:

Sub alternateAlignLeft()
For rowx = 2 To 1000 Step 2
    Cells(rowx, "J").HorizontalAlignment = xlLeft
Next
End Sub

Sub alternateAlignRight()
For rowx = 2 To 1000 Step 2
    Cells(rowx, "J").HorizontalAlignment = xlRight
Next
End Sub

To explain: Cells takes two arguments: RowIndex and ColumnIndex. So you performed your formatting on column 1, which is column A. The .Select statement which came before actually doesn't affect this line at all as there is no reference to Selection.

Note that you should also qualify all your Range objects, like this:

ThisWorkbook.Sheets("Sheet1").Cells(rowx, "J").HorizontalAlignment = xlRight

This needs to be adjusted to your use case of course.

score 0 · Answer 2 · answered Sep 27 '19 at 14:44

Your Columns("J:J").Select isn't having any effect because you are then selecting the specific cell with:

 Cells(rowx, 1).HorizontalAlignment = xlLeft

The Cells(rowx, 1) means the cell on row "rowx" in column 1... which is A.

If you want it to be J, you will need to change it to 10:

Cells(rowx, 10).HorizontalAlignment = xlLeft

and

 Cells(rowx, 10).HorizontalAlignment = xlRight

Why is this only applying the format to column A instead of the selected column J

2 Answers2