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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.
But, 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 fill color doesn’t overlap cell borders. Follow the steps below to be able to do that.
- 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.
- 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.
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. Do visit our ExcelDemy blog to explore more on excel. Stay with us and keep learning.