Thanks for the feed back and glad to help.

A hint:

The reason for Offset 'AND THEN' Resize is that Resize does not accept a
minus. So you have to go the cell of choice, by Offset which does accept
minus (left), and then indicate by resize, the cells you want to affect,
select, format...etc.

Fun
HTH
Regards
Howard


"robert morris" wrote in message
...
L. Howard,

Beautifully written explanation! As I said, I browse the discussion group
trying to learn from real questions asked by real people seeking real
solutions. I installed the Code and have now had fun changing numbers to
see
what happens.

Thanks ever so much.

Bob


"L. Howard Kittle" wrote:

Hi Robert,

It would look light this: New code below.

i - would remain the same because it refers to column B
j - is now 25 which is the last row

So the If statement below asks if the active cell is less than row 8 or
greater than row 25. If either is true then exit sub, it out of the
range.

Then there is the activecell resize code.
k = the activecell column number, lets say it is column E which is 6.

From the activecell offset 0 rows and since you are going to the left you
use a minus - in front of the column offset. So... -(k - i) k = 6 - i
=
2 would be minus four columns to the left which is B... that is good.

Now to Resize, we want 1 row and 6 columns to be highlighted. (If you
change the 1 to a 2 it will highlight the activecell row and the one
below
it.)

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 6).Interior.ColorIndex = 35

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 25
k = ActiveCell.Column()
Set Data = Range("B8:G25")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 25 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 7 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 6).Interior.ColorIndex = 35

End Sub

HTH
Regards,
Howard

"robert morris" wrote in message
...

L. Howard,

I'm just trying to learn and like your VBA. What would I change if I
had
a
Range of B8:G25 ?

Bob M.


"L. Howard Kittle" wrote:

Hi again Shannon,

Perhaps in the future you may want to highlight a row but contain it
in a
certain range. Try this, which I believe I authored. Click on any
cell
in
range B8:K22 and that row will be highlighted From columns B to K.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

HTH
Regards,
Howard

"Shannon" wrote in message
...

I would like to highlight/shade the entire row that currently has
the
active/selected cell. For example, whenever I am anywhere on row 10
I
would
like all of row 10 to be highlighted or shaded a certain colour. I
have
been
looking for a function like ACTIVEROW or ACTIVECELL so that I can
then
use
conditional formatting, but can't find anything.

I am using EXCEL 2007.

Please help. Thanks.