How to use VBA to change the active cell color in an Excel sheet

The default lively cell format in Microsoft Excel is not all that simple to see, however you should utilize this easy-to-implement code resolution to alter that.

excel.jpg

Picture: PixieMe/Shutterstock

By default, the lively cell in Excel is famous by a inexperienced border. There isn’t any spotlight or shading. Even in case you change themes and colours, the lively cell stays the identical. As is, it is notable and doubtless simple to seek out for many of us, however that does not imply you may’t change it. So far as I do know, there is not any built-in theme or type that permits you to management the lively cell. Happily, you are able to do so with VBA. On this article, I am going to present you a VBA process that adjustments the format properties for the lively cell.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should utilize earlier variations. Nonetheless, Excel On-line would not assist VBA. You’ll be able to work with your personal file or obtain the demonstration .xlsm, .xls and .cls information for simple entry to the code.

This isn’t my unique code; this process and others prefer it have been obtainable on-line for years. If I knew who to credit score, I might. 

Extra about Excel’s lively cell

When you choose a cell, Excel shows a inexperienced border round that cell, the lively cell. Once you transfer to a different cell or vary, Excel removes the inexperienced border from the earlier lively cell and applies it to the present lively cell. If you choose a variety, Excel applies that inexperienced border to all the vary. The lively cell in that vary continues to be white (or clear), however Excel applies a grey shade to the remaining cells, as proven in Determine A. If this is not sufficient for you or your customers, you should utilize a VBA process to alter the way in which Excel visually denotes the lively cell.

Determine A

excelactivehighlight-a.jpg

Search for the inexperienced border to seek out the lively cell. 

In regards to the VBA process

No matter your cause, you may simply change the fill shade for the lively cell utilizing VBA’s Worksheet_SheetSelectionChange occasion. This occasion is triggered when the choice adjustments on any worksheet—briefly, if you change the lively cell. (It would not work on a chart sheet.) What you may’t do, to my information is change the border shade. The Vary object (Goal within the process) does have a Border property, however it would not play nicely with the interior lively cell border property. You will notice the brand new border shade for less than a short second between alternatives. The process within the demo information has statements which are commented out; you may see how working with the border works by eradicating the remark character (‘) initially of those statements. 

The process in Itemizing A makes use of a static variable, rngOld to trace the present and the earlier lively cells. The present lively cell turns into the earlier when you choose a special cell. 

Itemizing A

Personal Sub Workbook_SheetSelectionChange( _

    ByVal Sh As Object, ByVal Goal As Excel.Vary)

‘Change the formatting properties for the lively cell.

Static rngOld As Vary

On Error Resume Subsequent

‘Colour of lively cell, handed as Goal.

Goal.Inside.ColorIndex = 8 ‘Cyan

‘Resets earlier lively cell to no shade.

rngOld.Inside.ColorIndex = xlColorIndexNone

‘Units static vary to Goal, the brand new lively cell.

‘Subsequent choice, rngOld would be the earlier

‘lively cell.

Set rngOld = Goal

Finish Sub

It is essential to notice that rngOld is a static variable. Meaning it retains its worth even after the process is finished. Different variables lose their worth when the process ends. Goal is handed to the process as the present lively cell. That cell’s fill shade is about to Cyan (a neon blue). rngOld, which is the earlier lively cell is about to nothing—in impact, eradicating the non permanent format. Lastly, rngOld is about to Goal. As a static variable, if you change the lively cell, rngOld will equal the earlier lively cell. I do know it’s kind of complicated however stepping by way of the code utilizing a breakpoint and F8 would possibly clear issues up for you, if you know the way. Break up your display screen and watch the cells replace as you step by way of the code. 

Working the VBA process

To enter the process, press Alt + F11 to open the Visible Fundamental Editor (VBE). Within the Venture Explorer to the left, choose ThisWorkbook so you may run the process in any sheet. You’ll be able to enter the code manually or import the downloadable .cls file. As well as, the macro is within the downloadable .xlsm, .xls and .cls information. In case you enter the code manually, do not paste from this internet web page. As an alternative, copy the code right into a textual content editor after which paste that code into the ThisWorkbook module. Doing so will take away any phantom internet characters which may in any other case trigger errors.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

In case you are utilizing a ribbon model, remember to save the workbook as a macro-enabled file. In case you’re working within the menu model, you may skip this step.

Return to Excel and begin clicking cells. Every time you alter the choice, you set off the process, which adjustments the fill shade of the lively cell, quickly. Determine B reveals this with a single cell and a variety.

Determine B

excelactivehighlight-b.jpg

  Use this process to alter the fill shade of the lively cell. 

Additionally see

Recent Articles

spot_img

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox