How to preserve grid lines while filling color in Excel? (2022)

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Excel Charts
  • Word Tips
  • Outlook Tips

As we all known, when we fill background color to cells, the gridlines will be covered as well, in this case, how could we keep the gridlines while filling background color in Excel cells to get the following screenshot shown.

How to preserve grid lines while filling color in Excel? (1)

Preserve gridlines while filling color with VBA code

How to preserve grid lines while filling color in Excel? (2) Preserve gridlines while filling color with VBA code

How to preserve grid lines while filling color in Excel? (3)

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Normally, there is no direct way for us to solve this job, but, here, I can introduce a VBA code for you. Please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the left Project-VBAProject pane, double click ThisWorkbook under the expanded VBAProject, then copy and paste the following VBA code into the blank Module:

VBA code: preserve gridlines while filling color

Dim xRgPre As RangePrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next If Not xRgPre Is Nothing Then DrawBorders xRgPre Set xRgPre = TargetEnd SubPrivate Sub DrawBorders(ByVal Rg As Range)'Updateby Extendoffice 20160725 Dim xCell As Range Application.ScreenUpdating = False For Each xCell In Rg If xCell.Interior.ColorIndex = xlNone Then With xCell.Borders If .ColorIndex = 15 Then .LineStyle = xlNone End If End With Else With xCell.Borders If .LineStyle = xlNone Then .Weight = xlThin .ColorIndex = 15 End If End With End If Next Application.ScreenUpdating = TrueEnd Sub

How to preserve grid lines while filling color in Excel? (5)

3. And then save and close the code window, now, when you fill a color for a range of cells, the gridlines will be showed automatically.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

Sort comments by

Oldest First

Newest First

Comments (16)

No ratings yet. Be the first to rate!

dolphinks

#21912

This comment was minimized by the moderator on the site

Fabulous fix!!

Reply

Vic

#22307

This comment was minimized by the moderator on the site

(Video) Preserve grid lines while filling color in Excel

Works fine, but sadly every time I now click on a cell, the worksheet judders! Removed the VBA and the judder has ceased. Wonder why this should happen?

Reply

skyyang Vic

#22308

This comment was minimized by the moderator on the site

Hi, Vic,
There is no this problem in my Excel workbook, which Excel version do you use?

Reply

Report

Jeremy Jones Vic

#22309

This comment was minimized by the moderator on the site

Same here with Excel 2016. I'm getting freezing and mostly blanked out screen for several seconds, especially when selecting many cells. It's a shame because it does work perfectly otherwise.

Reply

Report

Terry

#22840

This comment was minimized by the moderator on the site

works but one has to correct the error if you see it.
Dim xRgPre As Range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not xRgPre Is Nothing Then DrawBorders xRgPre
Set xRgPre = Target

*************************************
Should READ... as the If statement only ensures that something is there to set to memory allocation.

One could set the next line below for clean up and not allow memory leaks.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Set xRgPre = Target
If Not xRgPre Is Nothing Then DrawBorders xRgPre
'For ensured memory leaks do not happen
'Clean up the memory allocated after it is not needed.
Set xRqPre = Nothing

Have a nice day..

Reply

James Godesky Terry

#22841

This comment was minimized by the moderator on the site

Does not work with Conditional Formatting. Set xRqPre = Nothing gives error: variable not defined (deleted the line).Screen jiggles here too, using 2010. Other wise its a cool tool..

Reply

(Video) VIDEO #29 (Excel 2010) - Using fill colors and grid lines in Excel

barbatus

#22846

This comment was minimized by the moderator on the site

use only standard colors ;)

Reply

Andrew

#24417

This comment was minimized by the moderator on the site

Cool, but now if I fill some cells, and hit Ctrl+Z to undo, it won't work. Will not undo.

Reply

skyyang Andrew

#24418

This comment was minimized by the moderator on the site

Hello, Andrew,
Yes, the above code can not support Undo feature, you should fill no color manually to undo your work.
Thank you!

Reply

Report

(Video) Learn Excel - Gridlines Missing from 1 Range - Podcast #1887

Alissa

#25936

This comment was minimized by the moderator on the site

Another fix, if you don't want to mess with code:
First figure out which color gray matches the default lines and have it in mind.
In the Home tab click Cell styles, right click on Normal, click Modify -> Format -> Border. For Line Color, select that gray. For Presets, select Outline. Done.
Now you may have to select all (CTRL+A / Command+A) and click Cell styles -> Normal to make sure it's applied to all cells. When you fill a cell, the borders will remain.

Reply

Armaan Sandhu Alissa

#25937

This comment was minimized by the moderator on the site

Thank you!!

Reply

Fox Alissa

#25938

This comment was minimized by the moderator on the site

Alissa , I had the same problem couldn`t get it fixed but your reply helped me a lot , so thank you very much , you are genius .

Reply

JL Alissa

#25939

This comment was minimized by the moderator on the site

This helped me SO much. I have literally been searching so many places and this is the only explanation that worked! Thank you!!!

(Video) How to Show/Hide Grid Lines in Excel - Grids Not Showing Up in Excel Worksheet - NO ADS

Reply

Jane Alissa

#39049

This comment was minimized by the moderator on the site

Very helpful- thank you

Reply

Chad

#32797

This comment was minimized by the moderator on the site

Thank you! I was pulling hairs trying to get the nice grey-ish borders to stay after colorizing. Can't believe Microsoft hasn't already implemented this.

Reply

S Culbertson

#35387

This comment was minimized by the moderator on the site

This works great. (Thanks) But here's the real question - Why does Excel allow gridlines to disappear when filling cells with color in the first place? Just another dumb glitch in the techy world that so-called intelligent developers never think of. I mean, the gridlines, by default are supposed to be there anyway...so....

Reply

Report

There are no comments posted here yet

Leave your comments

Posting as Guest

Login

(Video) Learn how to change the color of Excel's GRID-LINES | Vikas University Excel Tutorial

FAQs

How do you fill cells with color but keep gridlines? ›

the Excel grid lines are only visible when a cell has no fill. If you want to see grid lines on cells with a fill colour, you need to format the cell borders. Tip: Create a style that has the fill AND the cell borders applied, then you only need on click in the Styles panel to apply the look.

How do I preserve grid lines in Excel? ›

Click the Page Layout tab. To show gridlines: Under Gridlines, select the View check box. To hide gridlines: Under Gridlines, clear the View check box.

How do I fill in an Excel spreadsheet with lines? ›

Fill data automatically in worksheet cells
  1. Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
  2. Drag the fill handle .
  3. If needed, click Auto Fill Options. and choose the option you want.

How do you color Excel cells without losing gridlines? ›

Click File > Excel > Options. In the Advanced category, under Display options for this worksheet, make sure that the Show gridlines check box is selected. In the Gridline color box, click the color you want. Tip: To return gridlines to the default color, click Automatic.

What happens to gridlines when you use a fill color? ›

If you apply a fill color to cells on your worksheet, you won't be able to see or print the cell gridlines for those cells. If you want to see or print the gridlines for these cells, you must remove the fill color.

Why do gridlines disappear in Excel when printing? ›

If gridlines don't appear when you preview or print the sheet, on the File menu, click Page Setup. Then on the Sheet tab, make sure that the Draft quality check box is not selected. If gridlines don't print, there may be an issue with your printer driver.

How do you copy and paste in Excel and keep the gridlines? ›

The gridlines can be included if you have Gridlines checked in Page Setup when you copy the Excel content. Alternatively, in Excel you can hold Shift while you open the Edit menu, select Copy Picture & choose the As shown on screen option in the little dialog that pops up on screen.

Why do gridlines disappear in Excel? ›

Gridlines Disappear in Excel When Color Overlay Is Set to White. If the Background Color of a cell is set to “White” instead of no Fill, then the Gridlines disappear in Excel.

How do I fill cells in Excel and keep gridlines? ›

Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 3. And then save and close the code window, now, when you fill a color for a range of cells, the gridlines will be showed automatically.

How do I fill a cell in Excel with color? ›

Select the cell or range of cells you want to format. Click Home > Format Cells dialog launcher, or press Ctrl+Shift+F. On the Fill tab, under Background Color, pick the color you want.

What is flash fill in Excel? ›

Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.

How do I highlight grid lines in Excel chart? ›

Right-Click Menu: Right-click the gridlines on the chart, and select More Grid Lines Options. The Format Horizontal Grid Lines dialog box opens. On the Minor Grid Lines tab, select Show Grid Lines.
...
The Major Grid Lines tab contains the following options:
  1. Show Grid Lines. ...
  2. Grid Style. ...
  3. Line Style. ...
  4. Tick Style. ...
  5. Tick Color.

What is the default gridline color in Excel? ›

By default, the gridlines in Excel come with a faint gray color. You can change the default color to any of your preferred colors by following the steps below: Click File on the top left corner then go to Options. In the Excel Options dialog box that opens, click Advanced on the left panel.

How do I restore GREY gridlines in Excel? ›

Troubleshooting: The Gridlines are Missing in Microsoft Excel - YouTube

How do I keep the highlight lines in Excel Mac? ›

How to Put Grid Lines on Excel for Mac
  1. Launch Excel and open the workbook containing the worksheet you want to edit.
  2. Click the tab for the worksheet you want to edit. ...
  3. Click the “Show” group on the View tab.
  4. Check the “Gridlines” check box in the Show group.

How do I Print gridlines in Excel without data? ›

Click on the File Tab and select Print. Within the first drop-down menu of the Settings, click Print Selection. Turn on the Print Gridlines feature by opening the Page Setup and completing the steps. Now, all selected cells will have gridlines around them, even the cells without data.

How do I format an Excel spreadsheet for printing? ›

Excel Print Formatting Tutorial - YouTube

How do I apply conditional color in Excel? ›

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to. On the Home tab, click Conditional Formatting. Click New Rule. Select a style, for example, 3-Color Scale, select the conditions that you want, and then click OK.

How do you lock a cell in Excel? ›

Lock cells to protect them
  1. Select the cells you want to lock.
  2. On the Home tab, in the Alignment group, click the small arrow to open the Format Cells popup window.
  3. On the Protection tab, select the Locked check box, and then click OK to close the popup.

How freeze panes Excel? ›

Freeze columns and rows

Select the cell below the rows and to the right of the columns you want to keep visible when you scroll. Select View > Freeze Panes > Freeze Panes.

How do you fix a broken line in Excel? ›

Clicking on "Advanced" can show you several advanced options to choose. If you scroll down, you can see the heading "Display options for this worksheet." One of the display options is "Show Page Breaks." If a check beside the option, clicking it to uncheck it should remove the dotted lines for you.

How do I keep fill color in Excel? ›

Fill cells with solid colors
  1. To fill cells with a solid color, click the arrow next to Fill Color. , and then under Theme Colors or Standard Colors, click the color that you want.
  2. To fill cells with a custom color, click the arrow next to Fill Color. ...
  3. To apply the most recently selected color, click Fill Color .

How do you fill color quickly in Excel? ›

Here is how you can use the F4 key to fill color in Excel:
  1. Select a cell in which you want to fill the color.
  2. Use the keyboard shortcut or the Fill Color icon in the ribbon to fill the color in the selected cell.
  3. Now select any other cell or range of cells that you want to fill with the same color.
  4. Hit the F4 key.

Why is color fill not working in Excel? ›

First, make sure that the cells you're trying to fill are actually selected. If they're not, the fill color won't show up. Next, check the fill color palette to see if the color you're trying to use is actually available. If it's not, you won't be able to use it.

How do I enable flash fill? ›

In most situations, Flash Fill kicks in automatically as soon as Excel establishes a pattern in the data you are entering. If a preview does not show up, you can activate Flash Fill manually in this way: Fill in the first cell and press Enter. Click the Flash Fill button on the Data tab or press the Ctrl + E shortcut.

Where is auto fill in Excel? ›

Click File > Options. Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off.

How do you flash fill in Excel without dragging? ›

Quickly Fill Numbers in Cells without Dragging
  1. Enter 1 in cell A1.
  2. Go to Home –> Editing –> Fill –> Series.
  3. In the Series dialogue box, make the following selections: Series in: Columns. Type: Linear. Step Value: 1. Stop Value: 1000.
  4. Click OK.

How do you fill a cell without losing gridlines? ›

Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 3. And then save and close the code window, now, when you fill a color for a range of cells, the gridlines will be showed automatically.

How do you shade a cell in Excel with lines? ›

Click the Format menu, and then click Cells. In the Format Cells dialog box, click the Fill tab.
...
Format the selected cells using the following options:
  1. In the Background color box, select a color.
  2. In the Pattern color box, select a color for the lines of the pattern.
  3. In the Pattern style box, select a pattern.

How do you copy and paste in Excel and keep the gridlines? ›

The gridlines can be included if you have Gridlines checked in Page Setup when you copy the Excel content. Alternatively, in Excel you can hold Shift while you open the Edit menu, select Copy Picture & choose the As shown on screen option in the little dialog that pops up on screen.

Why do my gridlines disappear in Excel? ›

Gridlines Disappear in Excel When Color Overlay Is Set to White. If the Background Color of a cell is set to “White” instead of no Fill, then the Gridlines disappear in Excel.

What is the default gridline color in Excel? ›

By default, the gridlines in Excel come with a faint gray color. You can change the default color to any of your preferred colors by following the steps below: Click File on the top left corner then go to Options. In the Excel Options dialog box that opens, click Advanced on the left panel.

How do you autofill colors in Excel? ›

Simply select the cells that you want to fill, press the Alt + H keys on your keyboard, and then press the F + I keys. This will open the "Fill" menu. From there, you can use the arrow keys to select the color that you want to use, and then press the Enter key to fill the cells.

How do you fill color quickly in Excel? ›

Here is how you can use the F4 key to fill color in Excel:
  1. Select a cell in which you want to fill the color.
  2. Use the keyboard shortcut or the Fill Color icon in the ribbon to fill the color in the selected cell.
  3. Now select any other cell or range of cells that you want to fill with the same color.
  4. Hit the F4 key.

How do I partially fill color in Excel? ›

Learn Excel from MrExcel Episode 905 - Shade Half a Cell - YouTube

How do you lock a cell in Excel? ›

Lock cells to protect them
  1. Select the cells you want to lock.
  2. On the Home tab, in the Alignment group, click the small arrow to open the Format Cells popup window.
  3. On the Protection tab, select the Locked check box, and then click OK to close the popup.

How freeze panes Excel? ›

Freeze columns and rows

Select the cell below the rows and to the right of the columns you want to keep visible when you scroll. Select View > Freeze Panes > Freeze Panes.

How do you fix a broken line in Excel? ›

Clicking on "Advanced" can show you several advanced options to choose. If you scroll down, you can see the heading "Display options for this worksheet." One of the display options is "Show Page Breaks." If a check beside the option, clicking it to uncheck it should remove the dotted lines for you.

Videos

1. How To Change The Gridline Color, or Make the Gridline Not Visible in Excel With Ease! #Tutorial
(Brandon's Excel Tutorials)
2. Gridlines Color Issues Excel Tricks [Solved]
(virtualofficetrain)
3. How to Add Grid Lines to Your Excel Spreadsheet?
(Patel Vidhu)
4. How to Change Grid Line Colour in Excel
(FigureAssist)
5. How to Change Gridlines Color In Excel
(datharamesh)
6. Change the Color of Gridlines in a Sheet of Excel
(The Akshay)

Top Articles

You might also like

Latest Posts

Article information

Author: Margart Wisoky

Last Updated: 12/20/2022

Views: 6546

Rating: 4.8 / 5 (58 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Margart Wisoky

Birthday: 1993-05-13

Address: 2113 Abernathy Knoll, New Tamerafurt, CT 66893-2169

Phone: +25815234346805

Job: Central Developer

Hobby: Machining, Pottery, Rafting, Cosplaying, Jogging, Taekwondo, Scouting

Introduction: My name is Margart Wisoky, I am a gorgeous, shiny, successful, beautiful, adventurous, excited, pleasant person who loves writing and wants to share my knowledge and understanding with you.