Offset

[Martin]

Hello,

I am trying to write some code that will select row32 of the active cell.
This always needs to be row 32 becuase I want to get the interior colour
value of that cell and apply that to the active cell. Here is my code so far:

ActiveCell.Offset(Right(ActiveCell.Address, 2) - Right(ActiveCell.Address,
2) + 32, 0).Select

If I put this into a message box then it always returns 32 so I know that
works but it doesnt seem to work with the offset rule. Does anyone know why
this is?

I also need to get the value of the interior colour. i havent looked this
up yet but again if anyone can help with that it would be appreciated.

Martin

 

[Mike H]

Hi,

This will get the colorindex from row 32 of the activecolumn and set the
activecell to that colour

Dim cIndex As Long
cIndex = Cells(32, ActiveCell.Column).Interior.ColorIndex
ActiveCell.Interior.ColorIndex = cIndex

or to do it all in one line

ActiveCell.Interior.ColorIndex = Cells(32,
ActiveCell.Column).Interior.ColorIndex


Mike

Mike

 

[Ron Rosenfeld]

Hello,

I am trying to write some code that will select row32 of the active cell.
This always needs to be row 32 becuase I want to get the interior colour
value of that cell and apply that to the active cell. Here is my code so far:

ActiveCell.Offset(Right(ActiveCell.Address, 2) - Right(ActiveCell.Address,
2) + 32, 0).Select

If I put this into a message box then it always returns 32 so I know that
works but it doesnt seem to work with the offset rule. Does anyone know why
this is?

I also need to get the value of the interior colour. i havent looked this
up yet but again if anyone can help with that it would be appreciated.

Martin

Your questions are not clear.

What do you mean by:

"row32 of the active cell." ??

An active cell is a single cell. It does not have 32 rows.

Do you mean the row 31 cells below the row of the active cell?
Do you mean the row 32 cells below the row of the active cell?

Also, you then write "I want to get the interior colour
value of that cell and apply that to the active cell." But selecting a 'cell'
is NOT the same as selecting a 'row'

And, if all you want is to set the activecell to the same color as some other
sell, there is no need to select anything.

For example, if you want to use the interior color of the cell that is 32 cells
below the active cell, you could use something as simple as:

ActiveCell.Interior.Color = ActiveCell(32).Interior.Color

Just remember that the row index of the active cell is "1-based". So that if
activecell is A1, activecell(32) would be A32. And if activecell is A8,
activecell(32) would be A39.

--ron

 

[Martin]

Mike, thank you. Spot on.

Martin

Hi,

This will get the colorindex from row 32 of the activecolumn and set the
activecell to that colour

Dim cIndex As Long
cIndex = Cells(32, ActiveCell.Column).Interior.ColorIndex
ActiveCell.Interior.ColorIndex = cIndex

or to do it all in one line

ActiveCell.Interior.ColorIndex = Cells(32,
ActiveCell.Column).Interior.ColorIndex


Mike

Mike

 

[Mike H]

Glad I could help

Mike, thank you. Spot on.

Martin