Highlight active row & column within a defined range (Not a

[XL-Dennis]

This message was edited by XL-Dennis on 2002-11-14 17:29

 

[gnaga]

Hi Dennis. It is a nice job.

Here is a same type of result achieved by Ivan.

http://www.mrexcel.com/board/viewtopic.php?topic=9023&forum=2&start=0

GNaga

 

[rikrak]

Hi Dennis,

It works fine.
May I suggest that you check if named region "data" exists. I inserted your code, but forgot to name B2:F16 "data" and got an error 1004.

 

[Jack in the UK]

Hi Dennis

Always good to see guys work given away, make me happy ay rate.

There are many versions and ways to do this and i just love to see DIFFERENT ways and ideas

Thank you - i cant say whats best as they are all different this board is littered with examples all that work, i say depends on just what you want.

Jack

 

[XL-Dennis]

>
This message was edited by XL-Dennis on 2002-11-14 17:31

 

[verluc]

On 2002-09-22 06:28, XL-Dennis wrote:
Hi Board,

From time to time I see queries about highlightning active row and column.

In general I like this approach because it´s support the users, especially when working with larger data-tables etc.

However, most of the answers shows how to highlight the whole active row as well as the whole column.

The procedure below shows a way to only highlight active row & column within a defined range.


<PRE>
<FONT color=blue>Private <FONT color=blue>Sub </FONT></FONT>Worksheet_SelectionChange(<FONT color=blue>ByVal</FONT> Target<FONT color=blue> As</FONT> Range)

<FONT color=#ff0000>'Purpose: Highlight the active row and column within a limit range.
</FONT>


<FONT color=#ff0000>'Place this procedure into a sheet-module.
</FONT>


<FONT color=#ff0000>'The name Data refer to the range: B2:F16
</FONT>
<FONT color=blue>Set </FONT>Target = Range("Data")



<FONT color=#ff0000>'Reset the rangecolour to no colour.
</FONT>
Target.Interior.ColorIndex = -4142



<FONT color=#ff0000>'No action taken unless the active cell address is within the defined range.
</FONT>
<FONT color=blue>If </FONT>Intersect(Target, ActiveCell) Is<FONT color=blue> Nothing</FONT><FONT color=blue> Then </FONT><FONT color=blue>Exit Sub</FONT>



<FONT color=#ff0000>'Add color to the row from the active cell to the B-column.
</FONT>
Range(Cells(ActiveCell.Row, "B"), Cells(ActiveCell.Row, _

ActiveCell.Column)).Interior.ColorIndex = 40



<FONT color=#ff0000>'Add color to the column from the active cell to row 2.
</FONT>
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(2, _

ActiveCell.Column)).Interior.ColorIndex = 40

<FONT color=blue>End Sub</FONT>






</PRE>

I don´t claim that this is a uniqueor a smart solution just only a practical solution to support users.

All comments are welcome

Kind regards,
Dennis

Hi,

For those who can use it,here in another one

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim InRange As Range
Cells.Interior.ColorIndex = xlNone
Set InRange = Application.Intersect(Target, Range("N8:AX25"))
If InRange Is Nothing Then Exit Sub
Range(Cells(Target.Row, 14), Cells(Target.Row, Target.Column)).Interior.ColorIndex = 3
Range(Cells(8, Target.Column), Cells(Target.Row, Target.Column)).Interior.ColorIndex = 3
End Sub