Activate range by Offset

[mnmhenry]

Hi all

how do I activte a range by using activecell.offset function....

ie

I want to hilight say A1:G1 to eventually color the cells red.....I know I can just go range A1:G1 activate........ But I first just want to activate A1, run the code using offset command color the cells and deselect....

I dont know how to activate the 7 cells all at once....

Thanks in advance
Mark

 

[Tom Urtis]

You can use Offset or Resize to select that range:

Range(ActiveCell, ActiveCell.Offset(0, 6)).Select

or

ActiveCell.Resize(1, 7).Select



Keep in mind you don't need to select the range to manipulate it, shading it red as you said:

Range(ActiveCell, ActiveCell.Offset(0, 6)).Interior.ColorIndex = 3

or

ActiveCell.Resize(1, 7).Interior.ColorIndex = 3

 

[SlinkRN]

Hi Mark,

try this:

sub OffsetandColor

Range("A1").select
'do whatever you were planning
ActiveCell.Resize(1, 7).Interior.ColorIndex = 3

Range("A1").Select

end sub


HTH, Slink
Oops, Tom already beat me to it!

 

[mnmhenry]

Thanks Tom & Slink, Woked a treat. I had that method in my mind but could not get the combination right... as for the resize command, I have never sen this before... How does this differ from Offset ?

Thanks again

 

[SlinkRN]

Hi again Mark,
Offset moves from the active cell a certain number of rows and columns:
activecell.offset(0,4).interior.colorindex = 3
will color the cell that is 4 columns over and 0 rows down.
activecell.resize(1,7).interior.colorindex = 3
will color 1 row and 7 columns over from the active cell.

You can play around with the row and column choices to color (2,7) etc. When using resize, it will color 2 rows and 7 columns.

Slink