Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Office > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-11-2012, 10:46 AM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 464
Jamal NUMAN is on a distinguished road
Question Is there a way to automatically highlight the column and the row that of the current

Is there a way to automatically highlight the column and the row of the current cell?

Iím wondering if there is a way to highlight the row and the column of the current cell.

For example, in the attached screenshots, I need a dynamic highlight for the row and the column of the current cell such that when the cell is changed then the column and the row of the new cell are highlighted.

The current behavior of the Excel is based on highlighting only the header of the column and the row of the current cell but what I need is to automatically highlight the entire column and row of the current cell.

Thank you in advance for the help

Best

Jamal
Attached Images
File Type: jpg Clip_38.jpg (156.5 KB, 5 views)
File Type: jpg Clip_39.jpg (151.6 KB, 4 views)
File Type: jpg Clip_37.jpg (138.6 KB, 4 views)
Reply With Quote
  #2  
Old 02-11-2012, 11:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows XP Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 634
Pecoflyer will become famous soon enoughPecoflyer will become famous soon enough
Default

Perhaps this link can get you on track
__________________
Happy with the answer ? Click on the scale to improve rep -A message to cross posters
Reply With Quote
  #3  
Old 02-12-2012, 02:09 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 464
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
Perhaps this link can get you on track

Thank you for the answer Pecoflyer. this does help.

the code that you have provided is working very well but what i need is to highlight also beyond the active cell as shown in the screenshots in my first post. this code highlight only above and to the right of the active cell. what i need is to highlight above, under, left and right of the active cell.

is that possible?

regards

Jamal
Attached Images
File Type: jpg Clip_83.jpg (143.1 KB, 3 views)
Reply With Quote
  #4  
Old 02-12-2012, 04:06 PM
zyzzyva57 zyzzyva57 is offline Windows 7 32bit Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 413
zyzzyva57 is on a distinguished road
Default

Try this code:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    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
Reply With Quote
  #5  
Old 02-12-2012, 11:48 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 464
Jamal NUMAN is on a distinguished road
Thumbs up

Quote:
Originally Posted by zyzzyva57 View Post
Try this code:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    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
Thank you for the answer zyzzyva57. that's perfect. this is exactly what I'm looking for.

thank you guys for the help

appreciated

Jamal
Reply With Quote
  #6  
Old 02-13-2012, 01:16 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Microsoft MVP
 
Join Date: Jan 2011
Location: UK
Posts: 382
Colin Legg will become famous soon enough
Default

Since you're using Excel 2010, you should probably change this line:
Code:
If Target.Cells.Count > 1 Then Exit Sub
To this:
Code:
If Target.Cells.CountLarge > 1 Then Exit Sub
Otherwise, if the user selected all of the cells on the sheet you will get a runtime overflow error.
Reply With Quote
  #7  
Old 02-13-2012, 12:22 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 464
Jamal NUMAN is on a distinguished road
Lightbulb

Quote:
Originally Posted by Colin Legg View Post
Since you're using Excel 2010, you should probably change this line:
Code:
If Target.Cells.Count > 1 Then Exit Sub
To this:
Code:
If Target.Cells.CountLarge > 1 Then Exit Sub
Otherwise, if the user selected all of the cells on the sheet you will get a runtime overflow error.
Thank you Colin for the contribution.

i didn't find any problem with the code provided by zyzzyva57

please have a look on the attached screenshot. the code is working very well on the Excel 2010.

regards

Jamal
Attached Images
File Type: jpg Clip_91.jpg (129.8 KB, 5 views)
Reply With Quote
  #8  
Old 02-13-2012, 03:24 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Microsoft MVP
 
Join Date: Jan 2011
Location: UK
Posts: 382
Colin Legg will become famous soon enough
Default

Hi Jamal,
Quote:
Thank you Colin for the contribution.

i didn't find any problem
If you select all of the cells on the sheet at once you will get a runtime error. It's a small detail, but it can easily happen if someone clicks on the square as indicated in the screenshot I've attached.

It's because the Range.Count property was originally designed for pre-2007 worksheets which were smaller. Specifically, the Range.Count property returns a 32-bit Long Integer which can hold a maximum value of 2,147,483,647, but a worksheet in a 2007/10 format file has 17,179,869,184 cells, which is too large to be held in a Long Integer type.

When the gridsize increased in the Excel 2007 release, it was accompanied by a new Range.CountLarge property which can handle the larger grid.
Attached Images
File Type: jpg Overflow error.jpg (48.7 KB, 4 views)
Reply With Quote
  #9  
Old 02-14-2012, 01:58 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 464
Jamal NUMAN is on a distinguished road
Thumbs up

Quote:
Originally Posted by Colin Legg View Post
Hi Jamal,

If you select all of the cells on the sheet at once you will get a runtime error. It's a small detail, but it can easily happen if someone clicks on the square as indicated in the screenshot I've attached.

It's because the Range.Count property was originally designed for pre-2007 worksheets which were smaller. Specifically, the Range.Count property returns a 32-bit Long Integer which can hold a maximum value of 2,147,483,647, but a worksheet in a 2007/10 format file has 17,179,869,184 cells, which is too large to be held in a Long Integer type.

When the gridsize increased in the Excel 2007 release, it was accompanied by a new Range.CountLarge property which can handle the larger grid.
thank you for the elaboration Colin Legg. your contribution is quite useful.

best

Jamal
Attached Images
File Type: jpg Clip_110.jpg (120.3 KB, 4 views)
File Type: jpg Clip_111.jpg (115.1 KB, 5 views)
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I change the horizontal scrollbar to scroll smoothly rather than column by column carpat Excel 0 01-10-2012 08:34 AM
How to call current PC date and/or current PC year KIM SOLIS Excel 2 11-04-2011 06:09 PM
Current view filter applies automatically lumisy Outlook 3 03-25-2011 05:44 AM
Auto insert current month's name and current year Styler001 Word 4 01-25-2010 05:40 PM
find - reading highlight - highlight all / highlight doesn't stick when saved bobk544 Word 3 04-15-2009 03:31 PM


All times are GMT -7. The time now is 11:23 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft