Highlight Selected Excel Rows with VBA Without Losing Formatting
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.

- 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.
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.
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.
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.
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
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
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.
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA code is retained.

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.

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.




