Excel Fix: Gridlines Disappear When Color Added: 2 Solutions

Method 1 – Add Cell Border If Gridlines Disappear When Color Is Added

Steps

Press ALT+T+O, or ALT+F+T, or select File >> Options to open the Excel Options window.
Go to the Advanced tab.
Scroll down to find the Display options for this worksheet section.
Mark the Show gridlines checkbox.
Click on the dropdown icon for Gridline color and choose Light Gray.
Press OK.

Click on the downward arrow above the upper-left corner of cell A1 to select the entire worksheet. If you need to do this only to a specific range, select that range of cells instead.
Apply all borders by selecting the All Borders icon from the Home tab below.

The gridlines are visible even though the background fill color is there. Those are not gridlines but cell borders. Those lines will remain visible even if you uncheck Gridlines from the View tab, as shown below.

Method 2 – Use Excel VBA Code to Fix Gridlines Disappearing Issue

Steps

Save the spreadsheet as a macro-enabled workbook.
Press ALT+F11 or select Developer >> Visual Basic to open the VBA window.
Double-click on the worksheet listed under Microsoft Excel Objects at the left of the window.

A blank module will open.
Copy the following code using the copy button.

Dim ColoredCells As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not ColoredCells Is Nothing Then DrawBorders ColoredCells
Set ColoredCells = Target
End Sub

Private Sub DrawBorders(ByVal Rg As Range)
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Rg
If Cell.Interior.ColorIndex = xlNone Then
With Cell.Borders
If .ColorIndex = 15 Then
.LineStyle = xlNone
End If
End With
Else
With Cell.Borders
If .LineStyle = xlNone Then
.Weight = xlThin
.ColorIndex = 15
End If
End With
End If
Next
Application.ScreenUpdating = True
End Sub

Paste the copied code on the blank module, as shown in the following picture.

Press CTRL+S and then ALT+Q to activate the code and return to the worksheet.

Apply a background fill color to a range of cells.
Cell borders, which look like gridlines, will be added automatically when you leave the cells.

Explanation of the VBA Code

Dim ColoredCells As Range
Defines the Range variable for the cells where we apply the fill color.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
This private sub-procedure will limit the activity of the code to the particular worksheet only.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Instead of double-clicking on this Workbook, use the above sub-procedure to apply it to the entire workbook.

On Error Resume Next
If an error occurs, go to the next statement.

Private Sub DrawBorders(ByVal Rg As Range)
This sub-procedure contains the code to apply cell borders.

Application.ScreenUpdating = False
It will make VBA work in the background and execute faster.

Application.ScreenUpdating = True
Screen updating is set to its default value.

Things to Remember

You must change the gridline color to make the cell borders look more like them.
Read the explanation of the code to use the proper code for worksheets or the workbook.

Download Practice Workbook

You can download the practice workbook using the download button below.

Show Gridlines with Colored Background.xlsm