Excel Fix: Gridlines Disappear When Color Added (2 Solutions)

This article illustrates how to fix the issue of gridlines disappearing when color is added in Excel. Gridlines are horizontal and vertical lines in Excel separating cells from each other. But, unlike cell borders, gridlines are overlapped by the background fill color of cells making them invisible. There is no way to fix this issue as it is a fault of Excel by design. But you can use two solutions as the possible ways around this issue. The following picture highlights the purpose of this article. Have a quick look through the article to see how to do that.

Why Gridlines Disappear in Excel When Color Is Added and How to Solve This Issue?

Gridlines are thin horizontal and vertical lines in Excel that separate cells from each other as you can see in the following picture.

However, applying background fill color to the cells makes the gridlines invisible as shown below.

Unfortunately, there is no actual solution to make the gridlines visible when fill color is added to the cells. But, you can apply the following ways-around if you need to add fill colors to cells and to show the gridlines too.

Solution 1: Add Cell Border If Gridlines Disappear When Color Is Added

You can use cell borders and make them look like gridlines in Excel. Because the fill color doesn’t overlap cell borders. Follow the steps below to be able to do that.

📌 Steps

First, press ALT+T+O, or ALT+F+T, or select File >> Options to open the Excel Options window.
Then go to the Advanced tab.
Next, scroll down to find the Display options for this worksheet section.
Then mark the Show gridlines checkbox.
After that, click on the dropdown icon for Gridline color and choose Light Gray.
Then press OK.

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

Now it looks like the gridlines are visible even though the background fill color is there. In reality, those are not gridlines but cell borders. Those lines will still be visible even if you uncheck Gridlines from the View tab as shown below. But, this will get the job done for now until MS Excel fixes this problem in the future.

Solution 2: Use Excel VBA Code to Fix Gridlines Disappearing Issue

You can automate the process by using Excel VBA. Follow the steps below to do that.

📌 Steps

First, save the spreadsheet as a macro-enabled workbook.
Then, press ALT+F11 or select Developer >> Visual Basic to open the VBA window.
Then double-click on the particular worksheet listed under Microsoft Excel Objects at the left of the window. Double-click on This Workbook instead if you want to automate the process for your entire workbook(not recommended).

After that, a blank module will open.
Then 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

Now paste the copied code on the blank module as shown in the following picture.

After that, press CTRL+S and then ALT+Q to activate the code and return to the worksheet.

Now apply a background fill color to a range of cells.
Finally, as soon as you leave the cells, cell borders looking like gridlines will be added automatically.

Explanation of the VBA Code

Dim ColoredCells As Range
Defines the Range variable for the cells where we will 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)

You can use the above sub-procedure instead if you double-click on This Workbook to apply it to the entire workbook.

On Error Resume Next
If an error occurs, then 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 from the download button below.

Show Gridlines with Colored Background.xlsm

Conclusion

Now you know the 2 possible solutions to fix the issue of gridlines disappearing when color is added in Excel. Please let us know if this has helped you to solve your problem. You can also use the comment section below for further queries or suggestions.

<< Go Back to Gridlines | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!