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

3 years ago 388

The default progressive compartment format successful Microsoft Excel isn't each that casual to see, but you tin usage this easy-to-implement codification solution to alteration that.

By default, the progressive compartment successful Excel is noted by a greenish border. There's nary item oregon shading. Even if you alteration themes and colors, the progressive compartment remains the same. As is, it's notable and astir apt casual to find for astir of us, but that doesn't mean you can't alteration it. As acold arsenic I know, there's nary built-in taxable oregon benignant that lets you power the progressive cell. Fortunately, you tin bash truthful with VBA. In this article, I'll amusement you a VBA process that changes the format properties for the progressive cell.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions. However, Excel Online doesn't enactment VBA. You tin enactment with your ain record oregon download the objection .xlsm, .xls and .cls files for casual entree to the code.

This is not my archetypal code; this process and others similar it person been disposable online for years. If I knew who to credit, I would.

More astir Excel's progressive cell

When you prime a cell, Excel displays a greenish borderline astir that cell, the progressive cell. When you determination to different compartment oregon range, Excel removes the greenish borderline from the erstwhile progressive compartment and applies it to the existent progressive cell. If you prime a range, Excel applies that greenish borderline to the full range. The progressive compartment successful that scope is inactive achromatic (or clear), but Excel applies a grey shadiness to the remaining cells, arsenic shown successful Figure A. If this isn't capable for you oregon your users, you tin usage a VBA process to alteration the mode Excel visually denotes the progressive cell.

Figure A

About the VBA procedure

Whatever your reason, you tin easy alteration the capable colour for the progressive compartment utilizing VBA's Worksheet_SheetSelectionChange event. This lawsuit is triggered erstwhile the enactment changes connected immoderate worksheet—in short, erstwhile you alteration the progressive cell. (It doesn't enactment connected a illustration sheet.) What you can't do, to my cognition is alteration the borderline color. The Range entity (Target successful the procedure) does person a Border property, but it doesn't play good with the interior progressive compartment borderline property. You volition spot the caller borderline colour for lone a little infinitesimal betwixt selections. The process successful the demo files has statements that are commented out; you tin spot however moving with the borderline works by removing the remark quality (') astatine the opening of these statements.

The process successful Listing A uses a static variable, rngOld to way the existent and the erstwhile progressive cells. The existent progressive compartment becomes the erstwhile erstwhile you prime a antithetic cell.

Listing A

Private Sub Workbook_SheetSelectionChange( _

ByVal Sh As Object, ByVal Target As Excel.Range)

'Change the formatting properties for the progressive cell.

Static rngOld As Range

On Error Resume Next

'Color of progressive cell, passed arsenic Target.

Target.Interior.ColorIndex = 8 'Cyan

'Resets erstwhile progressive compartment to nary color.

rngOld.Interior.ColorIndex = xlColorIndexNone

'Sets static scope to Target, the caller progressive cell.

'Next selection, rngOld volition beryllium the previous

'active cell.

Set rngOld = Target

End Sub

It's important to enactment that rngOld is simply a static variable. That means it retains its worth adjacent aft the process is done. Other variables suffer their worth erstwhile the process ends. Target is passed to the process arsenic the existent progressive cell. That cell's capable colour is acceptable to Cyan (a neon blue). rngOld, which is the erstwhile progressive compartment is acceptable to nothing—in effect, removing the impermanent format. Finally, rngOld is acceptable to Target. As a static variable, erstwhile you alteration the progressive cell, rngOld volition adjacent the erstwhile progressive cell. I cognize it's a spot confusing but stepping done the codification utilizing a breakpoint and F8 mightiness wide things up for you, if you cognize how. Split your surface and ticker the cells update arsenic you measurement done the code.

Running the VBA procedure

To participate the procedure, property Alt + F11 to unfastened the Visual Basic Editor (VBE). In the Project Explorer to the left, prime ThisWorkbook truthful you tin tally the process successful immoderate sheet. You tin participate the codification manually oregon import the downloadable .cls file. In addition, the macro is successful the downloadable .xlsm, .xls and .cls files. If you participate the codification manually, don't paste from this web page. Instead, transcript the codification into a substance exertion and past paste that codification into the ThisWorkbook module. Doing truthful volition region immoderate phantom web characters that mightiness different origin errors.

SEE: Windows 10: Lists of vocal commands for code designation and dictation (free PDF) (TechRepublic)

If you are utilizing a ribbon version, beryllium definite to prevention the workbook arsenic a macro-enabled file. If you're moving successful the paper version, you tin skip this step.

Return to Excel and commencement clicking cells. Each clip you alteration the selection, you trigger the procedure, which changes the capable colour of the progressive cell, temporarily. Figure B shows this with a azygous compartment and a range.

Figure B