Remove Pattern Without Affecting Cell Color or Gridlines

[Filibuster]

How can I remove a cell pattern without affecting the cell color or gridlines?

Here is what happened. I have a spreadsheet that is 100x100 cells. The
cells had various colors applied using the Format Cells/Patterns/Cell Shading
dialog. I temporarily wanted to flag these cells as DO NOT EDIT by adding a
pattern (light dots) using the Format Cells/Patterns/Pattern dialog. I went
back to remove the pattern using the same dialog, but the gridlines are no
longer visible. I looked in MS Help - they suggest clicking on the Fill tool
and selecting NO FILL. When I do this, the gridlines come back, but the cell
coloring is now gone. Is there a way to remove the pattern while preserving
the grid and original cell coloring? Thanks so much.

 

[ShaneDevenshire]

Hi,

When you have a fill color on a cell the gridlines are no longer visible.
The solution is to add Borders. Choose a light gray dotted border and match
it to the gridlines and it will probably look ok.

 

[Mike Rogers]

Filibuster

Select the range that you have cells within to fix. Go back to format
Cell/patterns/cell shading and select the one that has no pattern. (upper
left in xl2k) Ok out and it should have cleared all patterns and everything
else should be as it was.

Mike Rogers

 

[Tyro]

In Excel 2007 if you fill the cell with anything but pattern color, you can
remove the pattern by selecting the pattern style at the top left of the
selections and the fill color will remain. However, if you used Pattern
color to fill the cell, the patter color will be removed also. I don't think
previous versions of Excel had pattern color in the Fill tab in Format
Cells.

Tyro

 

[ShaneDevenshire]

Hi Tyro,

Earlier versions do have a pattern drop-down on the Patterns tab just like
2007, only difference in 2007 is you can use gradient fills and there are
about 16 million colors to choose from instead of 56.

 

[Filibuster]

Hi All,

Thanks for the replies - but I am still having problems. I should also
mention, the original spreadsheet used borders extensively, and adding lite
gray borders to simulate the gridlines will destroy them.

To see what I am talking about, try this. Open a blank work sheet.
Highlight a square block of cells. Format Cells... Patterns and click a
color OK. Then, highlight a larger square area that includes the colored
cells. Format Cells... Patterns and click a pattern OK. Now, highlight all
the same cells from step 2 and remove the pattern. You'll see the gridlines
are no longer visible.

I think the problem is that when removing the pattern, I want the checkbox
to show greyed out (like it is originally before you select pattern) instead
of picking the empty white "pattern".

Maybe I can do this through VBA.

 

[Filibuster]

I played with this a little more. If your selected range (with pattern)
includes cells with color, and cells without color - then your remove the
pattern - excel adds white as the cell color for cells that were originally
not colored. By adding the white cell color - the gridlines go away. Any
ideas how I can keep the colorless cells colorless? Maybe I need to write
some code...

 

[Tyro]

I use Excel 2007. I selected 4 cells then formatted Pattern Color then
Pattern Style (any but uppermost left style). Then I selected 16 cells
including the previous 4 and formatted Pattern Color and Pattern Style (any
but uppermost left style). I cannot get a pattern to appear without
selecting a pattern style. Then went to Format Cells - Fill and selected
Background Color - No color. The pattern colors and styles disappeared and
the gridlines remained. I can make the gridlines disappear by selecting the
upper left selection in Pattern Styles and bring the gridlines back by
selecting Background Color - No color. The uppermost left pattern style
hides the gridlines but does not apply a color.

tyro