Multiple non-contiguous cells selection in same column

[YaYa08]

Help please.
I need to go to the end of a column and highlight the cell in the forth and fifth row and then only the cell in row 21 of the same column.

Please advise how to write VBA for this. My End Right column changes weekly

Currently I have:

Range("C4").End(xlToRight).Offset(-1, -1).Select (which is the first cell)
I need to include the next cell in row below and the last cell in column which is row 21.

 

[Rick Rothstein]

Your description seems to be asking for a different starting cell than you code selects. Your code selects the cell in the next to last column on Row 3... you description seem to say the starting cell should be on Row 4 of the last column. Please clarify which column you want (last or next to last) and which starting row (3 or 4).

 

[YaYa08]

Sorry for the confusion. I'm a novice and use Macros to create my code.

I want row 3 as there are merged columns so I cannot use it to find the last column. So I use row 4 and go up to row 3 to begin.

Basically I need last cell in row 3 and last cell in row 4 and last cell in row 21. Each of the 3 cells will be getting a color code.

 

[Rick Rothstein]

Merged cells always cause problems so we need to know how you implemented them. Please describe which cells are merged so we will know how to give you code that will work. And describe what references you want and how you want to use them.

 

[YaYa08]

Range("C4").End(xlToRight).Offset(-1, -1).Select (which is the first cell) Which is last cell in Row 3

I also need last cell in row 4 and last cell in row 21. Each of the 3 selected cells will be getting a color code.

This will automate so I don't have to hard code each cell as in the current code below:

This is currently how it is now:

Range("AG3:AG4,AG21").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Thanks!

 

[Rick Rothstein]

Range("C4").End(xlToRight).Offset(-1, -1).Select (which is the first cell) Which is last cell in Row 3

This line of code is what has me wondering. From your description, you seem to want to work in the last column; however, the red highlighted -1 in your code above says to back up one column from the last column. So which do you want... the last column or the next to last column?

 

[YaYa08]

Sorry, this is getting too complicated.

I want the last cell in the last column of row 3 and the last cell in the last column of row 4 and the last cell in the column of row 21. Then I want to color code them to light green.

Since row 3 has merged cells, I went to row 4 and told it to go to the last cell in the last column, then backed up to row 3 so I am at the last cell in the last column of row 3 . I already have that in my code.

I am just trying to add:
last cell in the last column of row 4
last cell in the last column of row 21

When I identify those it is moving the focus to only the last cell in row 21. I need all 3 cells selected

 

[YaYa08]

PS. Thanks for being patient and working through this with me.

 

[Rick Rothstein]

You still did not answer my question your merged cells. Let's say the last column is Z (just for example purposes)... what cells in Column Z are merged?

 

[YaYa08]

Row 3: Columns A-M are merged and columns N-DA are merged. Column DB is not merged and is last cell in row3/columns that contains data