VBA & Macro Problems

Highlight Selected Excel Rows with VBA Without Losing Formatting

Emma BrownEmma Brown Nov 18, 2026 868 views

Question details

Highlight the row of the selected cell without removing existing formatting, and provide a shortcut to toggle bold formatting for the relevant cells.

Highlight Selected Excel Rows with VBA Without Losing Formatting
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses existing font and fill colors that should remain intact while selection highlighting and a formatting shortcut are added.
Observed behavior
The selected row should have a temporary highlight, and a shortcut should toggle bold on its relevant cells without clearing their fills.
Before you start

Choose the cells to highlight; this example uses A2:B100 and reserves XFD1 on the same sheet to store the selected row number. Make sure XFD1 is unused, and use desktop Excel with macros enabled.

Solution 1Recommended

Use conditional formatting for the selected row and a macro for bolding

Use a worksheet event to update a helper cell, then let conditional formatting display the temporary highlight without clearing cell fills.

The conditional formatting rule temporarily displays a green fill over the underlying fill; it does not delete the cell's original fill. Other conditional formatting rules can affect which color is visible.

The bold shortcut changes only the bold property of columns A and B. If those cells have different bold settings, the toggle uses column A's setting and applies the opposite setting to both cells.

1
Create the highlight rule

Select A2:B100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(ISNUMBER($XFD$1),ROW()=$XFD$1), choose a green fill under Format, and save the rule.

2
Add the selection event

Press Alt+F11, find the target worksheet in Project Explorer, and double-click its sheet object. Add this code to its code window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim hit As Range Dim eventsWereEnabled As Boolean eventsWereEnabled = Application.EnableEvents On Error GoTo CleanUp Application.EnableEvents = False Set hit = Application.Intersect(Target.Cells(1, 1), Me.Range("A2:B100")) If hit Is Nothing Then Me.Range("XFD1").ClearContents Else Me.Range("XFD1").Value = hit.Row End If CleanUp: Application.EnableEvents = eventsWereEnabled End Sub

3
Add the bold toggle

In the VBA editor, choose Insert > Module and add this macro: Public Sub ToggleRowBold() Dim c As Range Dim rowCells As Range Set c = ActiveCell If Intersect(c, ActiveSheet.Range("A2:B100")) Is Nothing Then Exit Sub Set rowCells = ActiveSheet.Range("A" & c.Row & ":B" & c.Row) rowCells.Font.Bold = Not rowCells.Cells(1, 1).Font.Bold End Sub

4
Assign a shortcut and test

In Excel, press Alt+F8, select ToggleRowBold, and choose Options. Enter an uppercase B to assign Ctrl+Shift+B. Select a cell in A2:B100, press the shortcut twice, and move the selection to confirm that the temporary highlight moves and the original fills remain.

5
Save the workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA code is retained.

Use conditional formatting for the selected row and a macro for bolding
Check rule priority: Use Home > Conditional Formatting > Manage Rules to inspect overlapping rules. The selected-row color may temporarily hide an existing fill or conditional color; move the selection away to see the underlying formatting again.
Free Microsoft Office alternative

Work with formatted spreadsheets in WPS Office

WPS Office is a free, lightweight Microsoft Office alternative with a familiar interface for opening and editing spreadsheets. Its format compatibility helps you move existing files into WPS, although VBA event and macro support can vary by version and this Excel-specific solution is not guaranteed to run there.

Use familiar spreadsheet formatting tools to work with row and cell colorsOpen and edit Microsoft Excel formats such as XLSX and XLSUse a free, lightweight office suite with a familiar interfaceMove existing spreadsheets into WPS Office without starting from scratch
microsoft office alternative - wps office

Frequently Asked Questions

Will the green highlight erase my existing cell colors?

No. The conditional formatting rule displays green temporarily without changing the underlying fill. Move the selection outside the row to see the underlying color again.

Can I use Ctrl+B instead of Ctrl+Shift+B?

Ctrl+B is Excel's built-in bold shortcut. Assigning a macro to it can replace its normal behavior, so the example uses Ctrl+Shift+B instead. Set the shortcut through Alt+F8 > ToggleRowBold > Options.

Why does another conditional color appear instead of green?

An overlapping conditional formatting rule may take priority. Open Home > Conditional Formatting > Manage Rules and review the rule order and any Stop If True settings.

What happens if I select multiple cells?

The event checks the first cell of the selection. If that cell is in A2:B100, its row is highlighted; otherwise, the temporary highlight is cleared.

Can the shortcut apply yellow without replacing an existing fill?

A direct yellow fill would replace the underlying fill. To preserve it, use a separate conditional formatting rule and a marker for the yellow state, then manage its priority alongside the green selection rule.