HIGHLIGHTING ROWS & COLUMNS

  • Thread starter Thread starter Darren Fall
  • Start date Start date
D

Darren Fall

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?
 
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.htm...y=conditional+formatting&mode=Match+ALL+words

and John:
http://search.atomz.com/search/?sp-q=conditional+formatting&sp-a=000336ae-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.
 
Dim oRng As Range
Set oRng = Union(Columns(sCol), Rows(iRow))
oRng.Select
Click to expand...

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

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

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

cells.Select
 
Wild Bill said:
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>
Click to expand...

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.
Click to expand...

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.
Click to expand...
although yours seemed quite direct to me.

Regards

Bob
 
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
 
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.
 
Yes but
Range(sCol & ":" & sCol & "," & sRow & ":" & sRow).Select
Range(sCol & "1:" & sCol & "65536" & ",A" & sRow & ":IV" & sRow).Select
is two selects
Click to expand...

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>
Click to expand...

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
although yours seemed quite direct to me.
Click to expand...

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.
 
No, they're two versions of [essentially] the same select. How about
giving them a try :)
Click to expand...

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
 
Back
Top