Free Microsoft Excel 2013 Quick Reference

Keep / restore gridlines with colored cells

When I format cells with different colors, the grey gridlines disappear. I know I can get them back by clicking "No Fill" on the "Fill Color" icon, but is there a way to get them in colored cells? I've tried a grey border, but I don't want the grids to print.

Post your answer or comment

comments powered by Disqus
Hi everybody,

I was looking for a solution through the excel questions but could not find anything which would help me.

As you can see in column B are two different colored Sun, red and magenta as well as Mon and Tue.

I would like to find a formula or macro without creating an extra column which looks for the color magenta i.e. if the particular cells are magenta (Sun, Mon and Tue) then perform an action.

I cannot use the formula in K7.

Please help.

******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18=
ABCDEFGHIJK1Month: 2Working - Hours 15% 35% 3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00 515Sat0:006:0006:000:006:0006:00 615Sat20:000:0004:0020:000:0004:00 716Sun0:006:0006:00 0:006:0006:00816Sun19:000:0005:00 19:000:0005:009?? 1022Sat20:000:0004:0020:000:0004:00 1123Sun0:006:00 0:006:0006:001223Sun19:000:00 19:000:0005:001324Mon0:005:0005:000:005:0005:00 1424Mon20:000:0004:00 20:000:001525Tue0:006:0006:00 0:006:001625Tue19:000:0005:00 19:000:001726Wed0:005:0005:000:005:0005:00 18 54:00 28:00 22:00Timesheet
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I have some data in which i am giving Red & Green Color due to my Importance. I want a data validation with both the color by which i can give select Red/Green as per my choice. Is it possible only color cell without data in data validation or any other formula is there?

I am trying to print an order from where I need the gridlines to print. I've
checked the print gridlines box but it just prints the gridlines for the
cells with text in them. How do I print the sheet with all gridlines showing?

Giday to you all, my first post.

I know there's no way of autofiltering colors with Excel 2003 but it can use function in VBA to identify the colors like 3 for red, 6 for yellow and so on.

I want to be able to insert a button on the "Summary" sheet to asign with macro that canautofilter the numbers in color cells in the same column.

Also with the codes I've put in VBA gave out strange numbers on blank cell which I don't want these awkard numbers to appear after typing in
=GetInteriorColorIndex(whatever the cell I type in)

Function GetInteriorColorIndex(pRange As Variant) As

    Set pRange = pRange.Areas(1)
    GetInteriorColorIndex = pRange.Cells(1, 1).Interior.ColorIndex
End Function

Function CountInteriorColor(pRange As Variant, pIndex As Integer) As Integer
    Dim LTestRange As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    Dim LTotal As Integer

    Set pRange = pRange.Areas(1)
    LTotal = 0
    m = pRange.Rows.Count
    n = pRange.Columns.Count
    LTestRange = pRange.Value
    For i = 1 To m
        For j = 1 To n
            If pRange.Cells(i, j).Interior.ColorIndex = pIndex Then
                LTotal = LTotal + 1
            End If
        Next j
    Next i
    CountInteriorColor = LTotal
End Function

Thank you inadvance

Using Excel 2003. I click on "file | page setup | sheet | gridlines" so
that the grids will show when printed.

Once I select certain rows or cells and fill them with color, those grid
lines don't show up any longer. I know I can choose to set "all borders" so
that grid lines will show, but then I run into other issues. Newly added
rows have to have "all borders" set again. If I select the entire worksheet
and set "all borders" then I know only filled in rows will show on the
printed copy. But if I go back and delete that row later, then I still have
extra bordered rows showing up on my print out.

Is there any way to use the "file | page setup | sheet | gridlines" with
color coded rows and cells??

Hello Everybody,

I am using Microsoft Excel 2003.

I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue.

Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?

If anybody knows, please answer and thank you in advance.

Hi everybody,

I have a macro for my Timesheet created by joefrench and would like to make some changes.

I'm a novise to VBA programming and need some help please.
In column B there are two different Sun marked red and magenta. I would like to have the macro modified so it also changes the color in red as in column B to color red in column K as performed already within the macro for the magenta color. As a reminder the red and magenta colors are not always at the same position.

How can that be done?

******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18E19H19K19=
ABCDEFGHIJK1Month:          2Working - Hours    15%  35%  3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00   
716Sun0:006:0006:00   0:006:0006:00816Sun19:000:0005:00   19:000:0005:009??         
1123Sun0:006:00    0:006:0006:001223Sun19:000:00    19:000:0005:001324Mon0:005:0005:000:005:0005:00   
1424Mon20:000:0004:00   20:000:0004:001525Tue0:006:0006:00   0:006:0006:001625Tue19:000:0005:00   19:000:0005:001726Wed0:005:0005:000:005:0005:00   
18    54:00  28:00  37:0019 Total - Hours  54.00  28.00

Hey guys

I have coded a function which fades the cells with colored background back to white by setting range.interior.color.

However, when I do this, these cells lose their gridlines. What do I have to do to avoid this?

I have attached an image, which shows cells with gridlines alongside cells which are "missing" their gridlines.

Dear Gurus,

I tried the VBA to hide the row if the range of cells e.g: C2:E2 are blank and the macro hide all rows if they find the conta of cells = 0 . But the issue that I want to keep those row that is blank but have at least one cell with color in back groud + comment.
1- Can I do that in VBA?
2- Can I check the empty range instead of counta cells in above condition for hide the rows?

Best regards,


I am at a loss here. I have attached a simplified working copy of my form.

I have searched every where I can find but keep coming up with sheet based formulas, I am looking for a VBA fix for this.

The Combobox selects the name of the person, and it populates the textbox with the associated date.

When the textbox is changed, I am *trying* to determine 3-4 conditions and color them accordingly.

I have thought to use conditional formatting, but it has to be based off of the selection from the Combobox, so that won't work.

1. If the checkbox is selected (automatically does based off of reading the cell color) then it will change the interior color to the bright green color (this works)

2. If the date is within a week from now, then color it yellow.. this doesn't work for me..

3. If the date is older than the current date, then color it red.. this doesn't work either

4. If the date is greater than the week out date, then leave it white.. this works, but only because the others don't I assume.

If anyone can help me out on how to compare dates that would be great.

Please excuse the messy code.


Hi Thanks for reading...

My question is:

How do I fill a cell with color, but I only want to fill one half of the cell. I am using Boarders to divide the cells in half on the diagonal. And I want to fill only one side of what now looks like a triangle.

Any thoughts? Can this be done?



I have a formula


For my if True result I want to have the D4 cell fill with color. Not sure if this is possible or not. Any ideas?

I am new to macros and have been trying for about a week to figure it out on my own so I thought I'd just ask for help.
Here is my situation:
I am making a quiz with excel, I have questions and pictures with colored cells below them, I have conditionally formatted them so that if the correct aswer is typed in the cell, it turns bright green. I want to keep a total count in a different cell of how many correct answers the user has gotten(how many cells have turned bright green). I have tried many modules w/ visual basic editor, posted online (sumcolor,countcolor, find shades, etc.) and none of them give me the result I am wanting. They seem to count the original fill color and not the color the cell turns after the format has been met. Also I would like for the count to update automatically instead of having to retype the formula to get the count to refresh (none of the ones I have found will do this, you must manually update the count on all of them). If anyone can help I would appreciate it, also if I can change the way my quiz is formatted, colors, text, the way the correct answers are displayed in order to get a total correct that would be fine.



Is there a way that I can program to fill a cell with color while using a
barcode scanner and the "find" feature? IE: the cells already have data in
them, and I use the "find" feature and a barcode scanner to locate certain
data cells. Is there a way that I can set it to fill that cell with color
automatically when I scan the barcode and it finds that cell?

I have a Excel 2003 spreadsheed with conditional formatting: a cell changes
from red, to green, to yellow, depending on the value entered into the cell
-- it works fine. When the spreadsheet uploads into SharePoint 3.0 (published
with spreedsheet functionality), the colored cells all change to a light red
color. What must I do to keep the color assignments in the Excel spreadsheet
so they appear the same when viewed in SharePoint?

Values in worksheet A are linked to cells in worksheet B. I need to
highlight certain values in A and have the highlight link to and remain with
the value in B. If I use a background color as a way to highlight certain
cells in A, the background color does not link to B. Also, I need the
highlight in B to be associated with the linked value in B - not the cell
location in B. If the highlight in B is associated with the cell in B, when
I do a row sort in B, the highlight becomes separated from the value in B.

Another way to pose the problem is to ask how to link formatted values with
formats in A with cells in B, so that the formatting in B is associated with
the vlaue in B, not the cell in B.

Like the title states I am looking for a means to find all rows that have a colored cell. My clients do not maintain any one color when setting a cell background. I am working in Excel 2007 and am aware of the sort columns by color and also have played with some VB to look at individual colors per column, but I was asked to find a means to sort all records to find ANY row with a cell that has a color.


I want to use an IF statement to check for text in a cell range and if true,
then fill a cell with a highlight color. In my application, I would look for
"PD" in invoice date fields and if found, highlight the amount invoiced in
green. I don't know the snytax to write the return value as "fill this cell
with color".

Hi All,

I'm not sure if I need to create a macro, write a VBA script or just make a simple equation, so I will start here and see what kind of response I get.

I am trying to create a horizontal string of cells that are merged with background color and have a name based on the job number and time it takes to run. If machine A produces 100 widgets in a given time I want that represented by a string of cells with each cell being one widget. Only the string needs to be named, not each cell.

Column 1 is the job number.
Column 2 is the machine.
Column 3 is the due date.
Column 4 is the quantity. This is the value that I want converted into a string of cells.

The name would simply be a combination of the first three columns.

Thanks for any help you can give.


Is there a way that I can program to fill a cell with color while using a
barcode scanner and the "find" feature? IE: the cells already have data in
them, and I use the "find" feature and a barcode scanner to locate certain
data cells. Is there a way that I can set it to fill that cell with color
automatically when I scan the barcode and it finds that cell?

I want to calculate how many cells are filled with color, how can

I do this?


Using VBA how would count the depth of a colored cell?
regardless of the rows and columns, keeping within range
of A1 ... D50

First scenerio:

1 Red
4 Red
100 Count 1 4

Second scenerio:

4 Red

100 Count 0 4

Hoping you can combine scenerio First and Second.

With Thanks any Help

Hello I just bought office 2003 professional and excel wont let me fill
cells with color.
also I am not able to add color to tabs. I havent noticed any thing else
so far. is this some setting Ive missed can anyone help ? Thank You

Hello Ė

I work with process maps. I have created a spreadsheet with 3 columns named ďManual TaskĒ ďWalkingĒ and ďWaitingĒ. In each of these columns I enter the time it takes to complete a task in minutes. Example: 1.5

To the right of these three columns I have columns that represent 30 seconds of time. Right now I would manually color in these cells based on the length of the task. Example: 1.5 minutes is 3 cells.

If the next task takes two minutes, I skip over the first three cells that I filled in with color for task number one, and fill in the next four cells with color.

The effect looks quite a bit like a MS Project timeline.

Can you think of a way to automatically fill in multiple cells with color based on the value I place in another cell? Example .5= 1 cell, 1.0=2 cells, 1.5= 3 cells, etc.
Is there a way to then to skip over cells populated by a previous row, and fill the next cells with color?

Any help would be greatly appreciated.

No luck finding an answer? You could always try Google.