Hi there,
There is an easier way to do this as well with a single line of code
in the Worksheets OnChange event,
Target.EntireRow.Select
It works pretty well.
Cheers
On Aug 4, 2:12 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> I don't know that there is any OPTION for this, but
> I have done it before with a MACRO.
>
> but there are a few "tricks"
>
> I use the Worksheet_SelectionChange event.
>
> First, record a macro in which you clear all formatting (highlighting)
> then "highlight" the row you wish to highlight.
> this will get you the syntax and color values.
>
> Use these values in the Worksheet_SelectionChange event like:
> '=========================================================
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> 'clear Highlight
> Range("A2:Z65000").Interior.Pattern = xlNone
> 'Highlight row
> Range("A" & Target.Row & ":Z" & Target.Row).Interior.PatternColorIndex =
> xlAutomatic
> Range("A" & Target.Row & ":Z" & Target.Row).Interior.ThemeColor =
> xlThemeColorAccent6
> Range("A" & Target.Row & ":Z" & Target.Row).Interior.TintAndShade =
> 0.599993896298105
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End Sub
> '=========================================================
>
> You could use:
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> 'clear formatting
> Range("A2:Z65000").Interior.Pattern = xlNone
> 'select entire row
> Range("A" & Target.Row & ":Z" & Target.Row).Select
> With Selection.Interior
> .PatternColorIndex = xlAutomatic
> .ThemeColor = xlThemeColorAccent6
> .TintAndShade = 0.599993896298105
> .PatternTintAndShade = 0
> End With
> 'return to selected cell
> Range(Target.Address).Select
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End Sub
>
> '=========================================================
>
> one trick here is with the EnableEvents.
> selecting a cell is an Excel "event".
> if, in the macro, you select all rows to remove the highlight,
> THAT is a selectionchange event!
> then when you select the row to highlight, that is ANOTHER event!
> so, you get caught in a loop of selectionchange events!
>
> To break this, I turn off the "event processing", make my hightlight
> changes, then turn in back on.
> turning on/off screenupdating eliminates screen "flicker"...
>
> hope this helps,
>
> Paul
>
> ________________________________
> From: Sheyn Lee <sheyn...@gmail.com>
> To: excel-macros@googlegroups.com
> Sent: Tuesday, August 4, 2009 4:59:02 AM
> Subject: $$Excel-Macros$$
>
> Dear All,
> Is there any option that when I select a cell in the sheet, the whole row
> corresponding to that cell is highlighted.
> In the attached example I have selected cell C7. Required: the whole row 7
> should be highligted.
> Thanks and regards,
> Sheyn
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
