highlight a selected row

radleyboo · Aug 8, 2008

not sure if that's the right description....

just bought a 22" wide screen monitor and when working in excel it's difficult to follow from left to right in a row i am looking at. i have 22 columns of info that are in view at any given time. the row number on the left gets highlighted and the cell i happen to be in is outlined with a double line border. is there a way to get the whole row outlined like the single cell?

i know i can achieve this by clicking on the row itself, but i tend to move a lot around the spreadsheet via the arrow keys.

Thanks

Smitty · Aug 8, 2008

Welcome to the Board!

You can do it with a change event, just note that it will wipe out any existing formatting:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)<br> Cells.Interior.ColorIndex = xlNone<br> <SPAN style="color:#007F00">' Highlight the active cell's Row & Column</SPAN><br> <SPAN style="color:#00007F">With</SPAN> ActiveCell<br> .EntireRow.Interior.ColorIndex = 36<br> .EntireColumn.Interior.ColorIndex = 36<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Peter_SSs · Aug 8, 2008

radleyboo

Welcome to the MrExcel board!

If you do actually want the border of the whole row (or rows) made similar to when you normally make a selection, rather than colouring the row/column as suggested by Smitty, then try this modification.

Notes:
1. Repeating Smitty's point: This will wipe out any existing borders you have on the sheet.

2. In case you need help with how to implement this:
- Right click on the sheet name tab and choose "View Code".
- Paste the suggested code into the main right hand pane that opens at the previous step.
- Close the VB window and start making selections on your sheet.

3. As described, this code will only apply to this sheet. Making it universally available to all sheets in all workbooks? Not sure how that could be readily achieved, and in any case would likely cause problems in many sheets because of the wipe-out of existing borders.

Yogi Anand · Aug 8, 2008

radleyboo said:
not sure if that's the right description....

just bought a 22" wide screen monitor and when working in excel it's difficult to follow from left to right in a row i am looking at. i have 22 columns of info that are in view at any given time. the row number on the left gets highlighted and the cell i happen to be in is outlined with a double line border. is there a way to get the whole row outlined like the single cell?

i know i can achieve this by clicking on the row itself, but i tend to move a lot around the spreadsheet via the arrow keys.

Thanks

Hi radleyboo:

Welcome to MrExcel Board!

If I understand you correctly, you may want to select the entire worksheet, and with cell A1 as the ActiveCell, use the following ConditionalFormatting formula ...

=LEN($A1)>0 ... use FORMAT option as desired.

pbt · Aug 8, 2008

My Welcome to you also:

Try this which I found on another forum. (ozgrid.com) And I give my apologies to the author, which I failed to make a note of.

Code:

Public pRule

 Sub butRulerToggle_Click()
    pRule = Not pRule
    Selection.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If pRule Then
        For Each aCell In ActiveSheet.UsedRange
            If aCell.Interior.ColorIndex = 37 Then aCell.Interior.ColorIndex = xlNone
        Next
        On Error Resume Next
        For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange)
            If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 37
        Next
    End If
End Sub

You will need to copy and paste into a standard module and "possible" put a button on your sheet, and assign it to this macro.

It will highlight whatever row you are on leaving any formating that you had as was.

If you need help, post back after trying.

Harry

EDIT: I think that I had taken this code and modified it so you don't need a button. Just need to click on a cell and it would highlight the row. Need to find my modification, if that is what you what.

Electra_Pink · Aug 28, 2008

hawaiian harry said:
EDIT: I think that I had taken this code and modified it so you don't need a button. Just need to click on a cell and it would highlight the row. Need to find my modification, if that is what you what.

I'd like to see your revision, HH.

I have used a combination of conditional formatting and VBA that gives you a line change as you move and it works slick and without replacing the existing cell formating -- but my spreadsheet is pretty big with a lot of formulas and so it is being bogged down with so many conditional formats. Therefore I am looking for alternatives. Here's where I got the solution I'm using, from ExcelChampion:

http://www.mrexcel.com/forum/showthread.php?t=247887&highlight=mycellformat

supdawg · Aug 28, 2008

radleyboo said:
not sure if that's the right description....

just bought a 22" wide screen monitor and when working in excel it's difficult to follow from left to right in a row i am looking at. i have 22 columns of info that are in view at any given time. the row number on the left gets highlighted and the cell i happen to be in is outlined with a double line border. is there a way to get the whole row outlined like the single cell?

i know i can achieve this by clicking on the row itself, but i tend to move a lot around the spreadsheet via the arrow keys.

Thanks

I use this on a selection

For Each Row In Selection.Rows
Row.Interior.ColorIndex = (Row.Row Mod 2) * 15
Next

Every other row will be gray.

radleyboo · Aug 29, 2008

pbt · Aug 29, 2008

Try this:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Sh.Cells.Replace What:="", Replacement:="", SearchFormat:=True, _
    ReplaceFormat:=True
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, Sh.UsedRange).Replace What:="", _
    Replacement:="", SearchFormat:=True, ReplaceFormat:=True
End With
End Sub

Steps to implement:

1. Open VBA window > Alt F11
2. In left pane of window, under Project - VBAProject, look for the name of your workbook. VBAProject (your workbook name)
3. Double click on ThisWorkbook and paste the above code in large window to the Right.
4. Press Alt-Q to return to your workbook.

This will highlight the row that your cursor is on in Yellow (ColorIndex=27). You may choose to use a different index number like Light Blue, 37, just be sure to change it in two places.

Harry

radleyboo · Aug 29, 2008

hawaiian harry

You made my day!
Works Great!

highlight a selected row

radleyboo

Board Regular

Excel Facts

Smitty

Legend

Peter_SSs

MrExcel MVP, Moderator

Yogi Anand

MrExcel MVP

pbt

Well-known Member

Electra_Pink

New Member

supdawg

Well-known Member

radleyboo

Board Regular

pbt

Well-known Member

radleyboo

Board Regular

Similar threads

Forum statistics

Share this page

highlight a selected row

Board Regular

Excel Facts

Legend

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

New Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock