Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 "Martin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
On Tue, 30 Dec 2008 03:14:21 -0800, Martin
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
Mike, thank you. Spot on.
Martin "Mike H" wrote: 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 "Martin" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
Glad I could help
"Martin" wrote: Mike, thank you. Spot on. Martin "Mike H" wrote: 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 "Martin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Programming | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
select offset (variable ,1) to offset(variable ,variable) | Excel Programming | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming |