Free Microsoft Excel 2013 Quick Reference

Cell background colors lost/corrupt

Occasionally a large workbook being used as a database on a network folder by several users (all excel 2007) seems to get corrupted and loses all the cell background color highlighting (the data and filters remain fortuantely). What causes this?

Post your answer or comment

comments powered by Disqus
I have Conditional Formatting applied to many cells, and I need to secure them through my VBA worksheet protection, but need to allow users to apply simple cell background color as necessay. If I include [I]AllowFormattingCells:=true[I] in my VBA Protect code, users have access to all format functions, including Conditional Formatting. Is there a way to "Git 'R Done" with VBA while Protect (with password) is active?

Hope someone can help,

How do I create a formula that control the cell background color, like

=if (b35 = 1, 'color is red',' no action')

Conditional formatting will allow upto 3 conditions. I have five. How
can you write an IF() statement/s to allow numerical values to change
cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will
you nest multiple IF's, the help files, aren't helping.

jrd269's Profile:
View this thread:

Can someone help me to write a formula to change cell background colors based
on a date.
I have created a training spreadsheet to inform me when individuals are due
for training. At my last job, I created the same spreadsheet but now I cannot
remember the formulas I used.
The cells would remain green if training was in toloerance, turn yellow when
training due date was 30 days out and turn red when the due date passed.
I used a nested if function for the dates but can't remember how to change
the colors.

Excel 2007

I had an Outlook reminder (5 weeks overdue) come up today prompting me to do
some work on getting Excel to do a conditional sum based on cell background
color. The thing is, I can't remember if I've already posted a question
about this, or if the reminder was intended to prompt me to post a question.
Either way, I can't find any indication that I have previously posed this
query. So, I apologize if this is a repeat question.

I have the following numbers in A2:A8. I've indicated the background color
for each value.

12 Green
42 Red
89 Green
36 Green
20 Red

I would like to know if there is a way to do a conditional sum based on the
background color of the cells. So, for example, a formula that keys on
green would produce a result of 137. I've found some information implying
that the following formula should work to sum all red cells:


However, that formula yields a #NAME? error, which I assume is caused by the
fact that Excel does not recognize the colorindex function. So I suspect
that colorindex is a custom function. My question then would be, where do I
get the colorindex function? I suppose that a second question would be, am
I on the right track?


How do I create a macro that would to pull up a menu to select any value range between 0 and 18, then locate the those values in cells AH51:AI90 that will match that selected value range, then change those cells background color to green, then all remaining cells in AH51:AI90 that did not match the values in the selected range, change it’s background color to red. The values are always whole numbers.

Selected range: 5 through 10
AH51, and AH53 cell background becomes green
AH52, cell background becomes red

Conditional formatting will allow upto 3 conditions. I have five. How can you write an IF() statement/s to allow numerical values to change cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will you nest multiple IF's, the help files, aren't helping.

Does anyone know how to stop the cell background color from printing. I use
cell background color to facilitate data entry in the sheet but I don't want
them to be printed in the hard copy. Thanks


Is there a way to change the default Border, Font Color, and Cell
Background Color in excel? By default it uses Underlined, Yellow, and
Red respectively.



Is there a way to automatically change a cell background color depending on a certain number?

Thank you

Is there a formula that will return a value based on a cells background color.

I have a spreadsheet that someone has taken the time to highlight changes they want. I want to be able to identify these and be able to filter on them. I assume the easiest way is to assign a value in an adjoining column.

I know I can do this the other way around by using conditional formatting, but don't know if what I want can be done.



The Macro I hope to create will work against a pivot table. I will explain below what I would like the Macro to do. I don't want to use Conditional Formatting because I want to add this piece of code to some existing vba code.

I have provided a sample of the data as part of this post. The name of the workbook is "efSample."

I have three column fields in my pivot table. The first column field is found in Cell A9 by the name of “Type” and what I would like the Macro to do is not only change the cell background color of the cell that contains the value "M & A” to Yellow, but also change the cell background color to yellow for all the cells right under "M & A" to yellow that are associated to the value "M & A". Then do the same for the value “People” except the color would be red instead of Yellow.

For example:
If the value of "M & A" is in cell A10 then all the cells under cell A10 (e.g. A11, A12, A13, A14) that are grouped with "M & A" would have a background color of yellow.



Is it possible to have a formula based on the cell background color. For instance , can I enter something like =if(A1(background color=yellow), 1, "")

I would like for it to return a 1 if the cell's background color is yellow and a null if not.

Thanks in advance.

I have a cell that uses white text and white background, so that users cannot
see the text. But, if they try to type in this cell, I want the text to show
up. So, how can I change cell background color (or text color) only when
cursor is hovering over the cell?


My current code changes the cell background color of all cells in Column A based on their value. I’ve attached the Excel Workbook (PivotTableReport) to this post. I want to modify this code so it meets the description below:

I have three pivot fields in my pivot table found on worksheet (Current Macro). The first pivot field is found in Cell A9 by the name of “Type” and what I would like the new Macro to do is change the cell background color of the cell that contains the value "Team Red” to Red and all the cells (A21) right under “Team Red” to Red that are associated to the value of “Team Red” (take a look at worksheet “After New Macro Visual” for a visual of what this would look like). The new Macro would then proceed to do the same for the value “People” and “M&A” with their respective colors. Again, the worksheet “After New Macro Visual” will give you a good visual of what I am looking for.

Current Code:

Sub ChangeCellColor()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value = "Team Red" Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value = "M&A" Then
cell.Interior.ColorIndex = 14
ElseIf cell.Value = "People" Then
cell.Interior.ColorIndex = 18
End If
End Sub

Hello Everyone! Is there any way to have the original cell background color also transfered/pasted along with the cell values.
Attached is a worksheet sample with macro, showing the actual output (can be rerun) and the desired output for clarification.

Sub CreateCommentsSummary()

    Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer
    Dim strSearch As String
     Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
     Set rgOutput = Range("Sheet1!E2")   ' output range fixed for example
    iRow = rgOutput.Row
    iCol = rgOutput.Column

    ' read each cell with comment and build the summary
    For Each rgCell In rgComments
      If InStr(rgCell.Comment.Text, strSearch) > 0 Then
        Sheets(1).Cells(iRow, iCol) = rgCell.Address  ' print cell address
        Sheets(1).Cells(iRow, iCol + 1) = rgCell.value           ' print cell value
        Sheets(1).Cells(iRow, iCol + 2) = rgCell.Comment.Text   ' print cell comment text
        iRow = iRow + 1
        End If
    Next rgCell

End Sub

Thanks in advance.

When i try to change th cell Background color with sylk format i gto an error
msg that says : " 'h.slk' may contain features that are not compatibile with
SYLK (Symbolic Link) "

i want to change the cell color & i 'm using oracle pl*sql to write the .slk
please help
Thanks in advance

(Windows XP Home SP 2 and Excel 2002) I have been using and updating an
Excel spreadsheet for several years and suddenly almost all of the cell
background colors have 'faded' to white. This may have been in consort with
my attempt to adjust my screen resolution, but I can't seem to re-find a
resolution that gets me back to 'the good old days', and I cannot 'color' the
backgrounds of Excel cells anymore. Also, it seems that a lot of fonts have
changed on websites; some are way too big and some are way too small, and
some of the website font background colors have also 'faded'. What's

Hello All : I need to change cell background color based on date settings. I need for cells to change color 30 days from a preset date in cell A10. Also need to change same cells a second time at 5 days from a preset date in cell A10. Purpose is to highlite expiration date to prompt action.

Using Excel 2007

Thanks All !

Excel Forum TOTALY ROCKS !!


Have such a problem. We had made pretty complicated survey. Answers for couple questions are represented with cells background color (red and yellow), in the same time the same cell contain a number which represents an answer to different question. There are about 2000 cells, that makes it impossible to count (not automatically) cells which are marked red or yellow. Is there any chance to do that automatically? Any formula?

Thanks in advance.

Ladies and Gentlemen,

First of all I would like to thank you for taking the time to read this and helping me.
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor.

What I'm trying to do:

Attached is the file I'm working with, to give you a better idea.

When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color.
For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black.

In addition, the background color of the non-black cells should change depending on the status.

OK : green
FAIL: red
N/A: orange
Exceptions: blue

The attached file shows a few possible results, manually entered.

Any help on this subject would be greatly appreciated.

I have reviewed much information about color pallets at various web sites and looked at various postings here and still have not sorted out the following problem:

I wish to allow a user to select the color of a rectangle drawn in VBA. I wish to have the user select a cell and use Format/Cells/Patterns and select a background color for the cell.

The VBA code (see below for test program) will then acquire the number associated with the background color of the cell and then add the rectangle shape designating the fill color using the previously acquired number.

The code works in that the color of the rectangle does change as the color of the cell changes by user formatting, but the colors of the cell and the rectangle are surprisingly not the same. I suspect that the pallet for the two entities (cell and rectangle) are different, but I am at a loss as to how to set the Workbook color pallet the same as the pallet the user sees when using Format/Cells/Patterns.

For example, if I make the background color of call A1 red, the color number of 3 is captured correctly by the program, but the ActiveSheet.Shapes command that sets the fill color of the rectangle has a different color (green) associated with color number 3.

Any suggestions?

CODE FOLLOWS====================================================

Option Explicit

Sub Graphics()

Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled

Dim cposnx As Double, cposny As Double, vlen As Double, vwid As Double
Dim ctr_x1 As Double, ctr_y1 As Double
Dim Rect1 As Shape
Dim veh1_color As Integer


' Determine the background color (interior) of the cell
' Workbooks("color_test.xls").ResetColors
veh1_color = ActiveCell.Interior.ColorIndex

cposnx = 200 ' x-coord of RF corner position of rect
cposny = 100 ' y-coord of RF corner position of rect
vlen = 100 ' rect length in screen units
vwid = vlen / 3 ' rect width in screen units

Application.ScreenUpdating = False

ctr_x1 = cposnx + vlen / 2
ctr_y1 = cposny + vwid / 2

' Draw a rectangle
With ActiveSheet.Shapes.AddShape(msoShapeRectangle, cposnx, cposny, vlen, vwid)
.Name = ("Rect1")
End With
ActiveSheet.Shapes("Rect1").Fill.ForeColor.SchemeColor = veh1_color

Application.EnableCancelKey = xlInterrupt
Application.ScreenUpdating = True

End Sub

I like to write a macro that can hide any row in a worksheet if the first cell in that row has a background color that is black. Any help will be appreciated.

I am having difficulty pulling background and font colors from one workbook
to a linked workbook... I have individual "weekly schedule" sheets for each
employee, and link them to a master timesheet which pulls only certain
information over to be used in scheduling meetings.... Everything is working
great except they employees place a background color in the cell to refer to
a project that is ontime, overbudget, misc... there are 7 colors they use as
I would greatly appreciate any help I can get, I have tried so many things
and cannot seem to pull colors over!

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