VPS for Traders





Search This Blog

Pages

Google

Highlight Selected Cells in Excel and Preserve Cell Formatting

Highlight Selected Cells in Excel and Preserve Cell Formatting

When you select a cell, or cells, in Excel, the row and column headers change color to indicate what you have selected. As you can see here we have selected B2. Or is it 2B? Hmm, 2B or not 2B?
Excel Default Highlighting
If you have a busy sheet though, you may want a more obvious indication of your selection. One approach is to use conditional formatting to do this. However the problem with this is that it changes the formatting of the selected cells. Not good when you need that formatting.
So how do you Highlight Selected Cells in Excel and Preserve Cell Formatting? By using shapes.
My approach is to draw shapes and overlay them on the column(s) and row(s) of the selected cell(s). Where these two shapes intersect is your selection.
You can use rectangles
Highlighting Cell using Shapes
but these cover the fill handle and prevent it from being grabbed. You can alter the height of the box so that it is clear of the fill handle, or you could just use lines
Highlighting Cell using Shapes
To do this requires VBA, and we use a workbook event Workbook_SheetSelectionChange event. Essentially all we are doing is moving the shapes around as we click on different cells.
You don’t need to know any VBA to use this. I’ve written all the code. You just need to copy and paste and you can be using this on your workbooks.
You can download a sample workbook with all the code here :
Note: these are .xlsm files please ensure your browser doesn't change the file extension on download.
You can also get the code as plain text :
As we need this code to work in all sheets we enter the subroutine (macro) into the ThisWorkbook module. Every time we select a different cell, the VBA code is executed.
This Workbook VBA Module

So How Does This Work?

The highlighting is achieved by drawing two rectangles, one over the cells in the selected row(s), and the other shape is drawn over the cell(s) in the selected columns(s). The intersection of the two shapes is your selection.
The first time we go to a sheet there won’t be any shapes so the VBA checks for the existence of the shapes, and draws them if they don’t exist. If you subsequently delete the shapes, or just one of them, they will be redrawn the next time you select a cell.

Special Selections

If you click on either a column or row header, Excel highlights that column or row in grey itself. So if this happens, the shapes are hidden by setting their .Visible property to FALSE. Next time you select a cell back on the sheet, the shapes are unhidden.
If you select the entire sheet either by CTRL+A or clicking the button at the top left of the sheet (between column header A and row header 1), the shapes are also hidden as Excel greys out the entire sheet. Just click back into a cell to see the shapes again.
Select Entire Sheet
If you select non-contiguous ranges, only the first range is highlighted. This may be something I can develop if there are enough requests for it.

Different Shape Formats on Different Sheets

If your default shape format doesn’t happen to be particularly visible on a sheet, you can change the formatting of the shapes on that sheet to suit. You can actually have different shape formats on every sheet in your workbook if you like.
Select the shape and then right click. You can change the style, fill and outline from the right-click menu. You can go nuts and add shadows, reflections and glows but really that will be just visible noise. All you really need is to change the line color, weight and whether you want a dashed, dotted or solid line.
Shape Format
Once you change the shape format, the shape retains the new formatting until you either change it again, or delete the shape. Once you delete the shape, it will be redrawn (using your default formatting) when you next select a cell.

Fills

Don’t add a shape fill.
If you do the fill sits over any selected cells and you will probably end up clicking on the shape(s) all the time. With no fill, the shape becomes ‘hollow’ and allows you to click through it to the cells below.

Line Colors

I’m using an RGB (Red, Green, Blue) value to set my line color. When I set .Line.ForeColor.RGB = RGB(146, 208, 80) I get a nice green and you know we like green on this site.
You can set your own RGB values and can use this site to figure out RGB values for your own colors.
Alternatively you can use in-built Excel color constants vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan so .Line.ForeColor.RGB = vbMagenta will give your shape magenta lines.

Changing the Default Shape Style

There are two section of code like this, one for the RowShape and one for the ColShape. These control how the shapes look when they are first drawn. If you don't like it then adjust the settings here.
Default shape style
The .Line.Weight value sets the thickness of the lines. I've got it at 2.
.Line.ForeColor.RGB specifies the RGB values for the color of the boxes.
If you don't like the default line style of a solid, continuous line, you can specify a value for .DashStyle. I've got it commented out (the apostrophe at the start of the line) as the default is a solid line and I am happy with that.
For the method using just lines, you can similarly adjust the line weight and color.

Shape Size

Shapes are drawn to the size of the visible range. What this means is that the shapes will extend from the left to the right of the visible cells in the sheet, and from the top to the bottom of the visible cells. Basically the shapes will be drawn to the edges of what you can see of your worksheet.
If you then zoom out, you’ll see the extent of the shapes. Click in another cell when zoomed out and the shapes redraw to fit your screen again.
The same thing will be noticed if you save the workbook on a computer with a small screen, and then open it on a computer with a large screen. But as soon as you click into a cell, the shapes are redrawn to fit the screen.

The Code

You can download a workbook with the code in it from here. I've tested this in Excel 2010 and 2013.
You can also get the code as plain text :

LifesDream - earn money each month





Verifield - Innovative Wealth Management

Verifield - your proven partners on stock markets Hasilkan uang dari Internet? Why Not? AVAZZY.com - Real business and real incomes

Alert Pay.. Get paid for subscribe...

DbClix Join 4Shared Now!

Liberty Reserve - Online Investment


Head Tail Game Liberty Reserve LR

Flip N Win

DepositFiles

Google

Get Paid to sign Up.

Internet Pay to Click

YOU can make $THOUSANDS of dollars per month...Starting from $0.00!

LinkGrand.com


Baca iklan .. dibayar loh...!

www.easyhits4u.com

EasyHits4U.com - Your Free Traffic Exchange - 1:1 Exchange Ratio, 5-Tier Referral Program. FREE Advertising! DonkeyMails.com: No Minimum Payout

Yahoo! Movies News & Gossip

Alexa

Alexa review on mInvestment