Highlight Row and column trough cell number - Row selection, not color changing rows

[Tiago Ferreira]

Hi guys!.

So, i have spreadsheet with some code that track my mouse x and y positions, and then puts in cell A1 the row number and in cell A2 the column number correponding off my mouse position.

What i need now is to take this row and column numbers from this cells, and use it to select the corresponding rows and columns.


I have found code to color change the rows and columns, but what i want is to select the rows and columns, like when you press them, an example here:

Like this:

Not like this:

Thanks in advance

 

[hiker95]

Tiago Ferreira,

Welcome to the MrExcel forum.

See if the following link will help you:

Cross-Hairs
Highlighting the Row and Column of the active Cell
Highlighting the Rows of Selected Cells (Microsoft Excel)

 

[Tiago Ferreira]

Thanks i will take a look

 

[Tiago Ferreira]

hi again, tkanks but the code in the link colors the cells and thats not what i whant.

i need to select the rows and columns Like when we press in the row number or column letter.

thanks



Example:

 

[Tetra201]

Try the following sub. Put it into the sheet code section, not in a module.

Sub Worksheet_Calculate()
    y = Range("A1").Value
    x = Range("A2").Value
    If IsNumeric(x) And IsNumeric(y) Then
        If (x > 0) And (x < 16385) And (y > 0) And (y < 1048577) Then
            Set Rng2Sel = Application.Union(Rows(y).EntireRow, Columns(x).EntireColumn)
            Rng2Sel.Select
        End If
    End If
End Sub

 

[hiker95]

Tiago Ferreira,

The following is from Microsoft, and, it does work correctly.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' https://msdn.microsoft.com/en-us/library/office/ff820716.aspx
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target
  ' Highlight the entire row and column that contain the active cell
  .EntireRow.Interior.ColorIndex = 8
  .EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub

Before you use the above code with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then click on a cell.

 

[Tiago Ferreira]

Hi!

Thnaks for the code, for some reason i cant get it to work, i put it in the code section but it does nothing for me...

I have macros enabled, and i have tested in my work spreadsheet and in a new spreadsheet and nothing... many thanks for your help.

 

[Tiago Ferreira]

hi Hiker.

thanks again but this still uses the interior.colorindex function....

i just want select the rows, or to have the same selection efect automatically when i press a cell, i have right now a code similar to this you give-me , but the problem i have a spreadsheet totally formated with colors and stuff and hi want to see the colors off the originals cell like they are, i just need this cross effect with row and column selection, just like when we press the number or letter off the row or column...

The behavior its equal to the code you give, but instead off coloring the cells, i want it to select the full rows and columns only

An example:

if i put this code it will select the intire row when i select a cell, that whatt i want, but i need to do the intire row and the intire column off the active cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.EntireRow.Select

Many thanks for your effort to help me

 

[Tiago Ferreira]

Hi Tetra!

Thanks for the code, for some reason i cant get it to work, i put it in the code section but it does nothing for me...

I have macros enabled, and i have tested in my work spreadsheet and in a new spreadsheet and nothing... many thanks for your help.

 

[Tetra201]

How are your cells A1 and A2 getting their values? -- That is, are there formulas in A1 and A2, or the values are being put directly by the other code?