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

Colin Legg · #1 02-13-2012, 02:16 AM

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.

Jamal NUMAN · #2 02-13-2012, 01:22 PM

Quote:

Originally Posted by Colin Legg

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

Colin Legg · #3 02-13-2012, 04:24 PM

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.

Jamal NUMAN · #4 02-14-2012, 02:58 PM

Quote:

Originally Posted by Colin Legg

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

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 09: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 06:40 PM
find - reading highlight - highlight all / highlight doesn't stick when saved	bobk544	Word	3	04-15-2009 03:31 PM