HIGHLIGHTING ROWS & COLUMNS
 

I have a worksheet that is 61 columns wide and 21 rows
high. I would like to be able to highlight the entire Row
and Column, relative to the cell that is active (up and
down, left and right), within this grid. Can anyone
suggest anything, perhaps in conditional formatting, that
might achieve this task?

HIGHLIGHTING ROWS & COLUMNS
 

You know selection is generally something to be avoided, but I assume
you really want it. Conditional formatting is one cool way but it would
seem to be "expensive" to have to format every cell on the sheet. AAR
for any such matters the first step should always be to check with Chip:
http://search.freefind.com/find.html...atch+ALL+words

and John:
http://search.atomz.com/search/?sp-q...6ae-sp00000000

You may be particularly interested in this instead of selection:
http://www.cpearson.com/excel/banding.htm

Since this is the programming area one programming solution is
sCol = Mid(ActiveCell.Address, 2, InStr(2,ActiveCell.Address,"$") - 2)
sRow = ActiveCell.Row
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select

If you want to limit it to 61 columns etc. modify the following.
Range(sCol & "1:" & sCol & "65536" & ",A" & sRow & ":IV" & sRow).Select

sCol is awkward because I don't know a direct way to get the column
letter. Perhaps someone else can supply that.

An alternate solution may be available with EntireRow et al.
You might poke around .Resize but I believe it's only for a rectangle.

HIGHLIGHTING ROWS & COLUMNS
 

Dim oRng As Range
Set oRng = Union(Columns(sCol), Rows(iRow))
oRng.Select

To be further anal, you can also replace the above with
Union(Columns(sCol), Rows(iRow)).Select

But nice code :)

HIGHLIGHTING ROWS & COLUMNS
 

Since this
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select

Selects the entire sheet, it would be easier to just do

cells.Select

--
Regards,
Tom Ogilvy

"Wild Bill" wrote in message
...
Thanks for the augmentation. Clever alpha parse there :) But I'll go
on a limb and say that
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
is only one operation, and saves a call to Union besides <g

The IV and 65536 were only to illustrate that he could substitute a
limited range, as I indicated. And his "grid" may not start in A1, so
he may need a tad of work coming up with the substitutions.

HIGHLIGHTING ROWS & COLUMNS
 

"Wild Bill" wrote in message
...
Thanks for the augmentation. Clever alpha parse there :) But I'll go
on a limb and say that
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
is only one operation, and saves a call to Union besides <g

Yes but
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
Range(sCol & "1:" & sCol & "65536" & ",A" & sRow & ":IV" & sRow).Select
is two selects, so the Union replaces the both. Agree that 2 selects may
well be less costly than a union (probably so small as to be
non-measuraeable though), but there is a natural aversion to selects (vbg

The IV and 65536 were only to illustrate that he could substitute a
limited range, as I indicated. And his "grid" may not start in A1, so
he may need a tad of work coming up with the substitutions.

By using the Columns and Rows properties, I avoid the need to put in any
cell references. I thought that this
sCol = Left(ActiveCell.Address(True, False), _
1 - (ActiveCell.Column 26))
was what you were referring to when you said
sCol is awkward because I don't know a direct way to get the column
letter. Perhaps someone else can supply that.
although yours seemed quite direct to me.

Regards

Bob

HIGHLIGHTING ROWS & COLUMNS
 

Perhaps this is version-dependent. I see a "black X" selected.

On Mon, 18 Aug 2003 09:13:10 -0400, "Tom Ogilvy"
wrote:

Since this
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select

Selects the entire sheet, it would be easier to just do

cells.Select

HIGHLIGHTING ROWS & COLUMNS
 

Tom,

I think it only selects the row and column of the activecell, not the whole
sheet (at least it does with me, and it seems to be what Bill is suggesting
will happen).

Regards

Bob

"Tom Ogilvy" wrote in message
...
Since this
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select

Selects the entire sheet, it would be easier to just do

cells.Select

--
Regards,
Tom Ogilvy

"Wild Bill" wrote in message
...
Thanks for the augmentation. Clever alpha parse there :) But I'll go
on a limb and say that
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
is only one operation, and saves a call to Union besides <g

The IV and 65536 were only to illustrate that he could substitute a
limited range, as I indicated. And his "grid" may not start in A1, so
he may need a tad of work coming up with the substitutions.

HIGHLIGHTING ROWS & COLUMNS
 

No, my mistake:

I typed it in as

Range("B:B","5:5").Select

Where it actually produces

Range("B:B,5:5").Select

subtle difference with dramatic difference in results.

--
Regards,
Tom Ogilvy

"Wild Bill" wrote in message
...
Perhaps this is version-dependent. I see a "black X" selected.

On Mon, 18 Aug 2003 09:13:10 -0400, "Tom Ogilvy"
wrote:

Since this
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select

Selects the entire sheet, it would be easier to just do

cells.Select

HIGHLIGHTING ROWS & COLUMNS
 

Yes but
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
Range(sCol & "1:" & sCol & "65536" & ",A" & sRow & ":IV" & sRow).Select
is two selects

No, they're two versions of [essentially] the same select. How about
giving them a try :)

but there is a natural aversion to selects (vbg

union or not, do you realize that you still select?!

I thought that this
sCol = Left(ActiveCell.Address(True, False), _
1 - (ActiveCell.Column 26))
was what you were referring to when you said
sCol is awkward because I don't know a direct way to get the column
letter. Perhaps someone else can supply that.
although yours seemed quite direct to me.

Having to use instr and mid does feel like a bit more work than ideal.
At least one of the pair would be avoided if there were a variable
(/property,etc.) with "$R" or ideally "R" instead of what .address
gives, "$R$2" (all specific columnar references I found were numeric,
not alphabetic). AAR you have some clever Boolean logic in its stead.

HIGHLIGHTING ROWS & COLUMNS
 

"AmendConstitution_ArnoldForPresident"
wrote in message om...

No, they're two versions of [essentially] the same select. How about
giving them a try :)

Of course you are right. I did try it, but I pasted both into my code, and
so they both executed, and I didn't immediately spot it. Noticed it almost
immediately I posted it. My mistake.

Regards

Bob