Free Microsoft Excel 2013 Quick Reference

Conditional Format Wont Save


I am having a problem with too many Conditional Formats. After trying to repair some broken Conditional Formats and save my workbook, I get this Microsoft Excel error message:

 formatting you recently added 
To (file name). 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was told that Excel has a maximum limit of 2050 rows for Conditional Formatting.

My sheet is a schedule withf 87 columns by 54 rows times 31, or a total of 145628 cells with Conditional Formatting.

Here is the code for the Conditional Formats:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
A complicating factor, I need this formula exactly the same (not the second row advancing one number) for each set of 2 rows.

Is there any way to do the same thing without Conditional Formating?

Any help would be most appreciated.


Post your answer or comment

comments powered by Disqus
I ran a very simple code that conditionally formatted red any cell sum greater than 12. The macro worked all the way to the end - about 15k rows. After saving and re-opening file only the first 1603 lines had the formatting and the rest did not. Several attempts end with no change in results.
I did the programming in Office 97 but edited and ran it on Excel 2002.
Any ideas?

Thank you,

John Adams

(Excel 2007) I have conditional formatting that is based on an array, applied
to all cells in a worksheet. The formatting underlines every third row after
monthly totals by:
=MOD(ROW()-MAX(ROW($A$1:$A1)*($A$1:$A1="Totals for")*($A1"Totals for")),3)=0
The words "Totals for" appear in col A, various rows. For test purposes I
have them in A41 and A78, and the word "Date" in A4, and the formatting is
When I save the workbook and re-open it, the wrong rows are underlined.
If I go to the "Edit rule" screen and click OK without changing the rule,
then Apply, the rule works correctly until next time I close and re-open the
workbook, when rows 42 and 78 are underlined. Editing the rule (without
changing it) and applying gets the correct rows, 44 and 81, underlined, but
the result is lost on saving.
Any ideas?

I have created quite an extensive excel file with a significant amount of
conditional formatting. I have reached the point where if I save now the
conditional formatting of the last few rows is not saved. Is there a way
around this or is there a limit to how much conditional formatting you can

I am currently using Microsoft Office Professional Plus 2010.

I am setting up conditional formatting based on the value of a cell equaling a value in another worksheet (contained in the same workbook) and these rules are not saving.

I have other conditional formatting that is NOT linked to another sheet and these rules will save.

What is going on?? What am I doing wrong?


I have made a fairly nice sized file (11 megs, no pivots,) and in one tab I have 6 columns of 2 condition formatting by 4000 rows. I was working and made some conditional formatting and then saved the file. Upon reopen the file took noticeably longer. (a minute instead of a few seconds) Knowing conditional formatting was the last thing I changed on the sheet I removed the conditional formatting and saved the file as "such and such version 2." Version two now opens at a normal rate but does not have conditional formatting. I have tried this twice and the same effect happens. It appears that conditional formatting slows the opening of this file.

Does anyone have any suggestions? I would like to include it before I send the file in. This specific tab has no formulas, and other tabs in the same workbook have conditional formatting (not anywhere near the scale though).

I have a very weird issue with a particular worksheet, which we are a few users sharing from a server on a read-only basis. We are using conditional formulas to highlight positive or negative values (green, red, etc). However, this sheet will for some reason when opening on my computer lose all conditional formatting, while my colleagues will have all the formatting kept. When I look under "manage rules" in conditional formatting, there are none. I have checked that we all run the SP1 version of 2007 excel, but I cant figure out what is the problem. Does anyone have an explanation on this very weird issue?

im trying to get 2 conditional formats to run at once but cant seen to get it to work, i've set it up to do the following

condition 1 is set to cell value is - - equal to - - ="removed" format as pattern with a cross hatch patern selected

condition 2 is set to formula is - - =ifCU12<>""+CU12<cv12 - - format as font colour red

where cu 12 contains a date i've put in manually and cv 12 contains the formula =today()

the cell with the conditional format would be blank, contain a date or contain the word "removed" without the quotes

i can get either of them to work individually but not together

what am i doing wrong?

Hi all, newbie here, I'm hoping someone can answer my query:

I have a spreadsheet at work in which I type in items via a raw material number, this then looks up a sheet and inserts the raw material name.

I would like for some of these numbers to come up in a different colour (i.e. flag them for other purposes). Conditional formatting won't work as there are approximately 30 of these numbers that need to be flagged, conditional formatting wont let me select a data range (nested data).

Is there some way that I can change the formatting on the lookup sheet for it to be carried over into the worksheet? There doesn't appear to be.

Or would an If statement (to change the font colour) be the way to go?

Many thanks in advance for any help, Cam.


Here's what i have:
Columns A-N will have various information in different formats (Drop down Lists, Numbers, Text, Currency, and Dates). Column M is my "Status" field where i have a drop down list with 7 different values (Paid, Billed, Etc.) that each have a associated color (Red, Green, Etc.).

What i want:
I want to be able to enter all information in Columns A-N and when i select from my "Status" list, for specific cells in the row to turn the associated color.

I.E. - Paid is Red so A2:B2 and D2:N2 would be red when i select paid form the "Status" list. And obviously i would need this to continue indefinitely.

I'm pretty sure conditional formatting wont work but if you have any ideas it would be a great help.

Thank you all!

I like the way that Excel 2007 handles conditional formatting for
overlapping ranges and such, but sometimes I have to save files
in .xls format. Invariably I get a warning that the conditional
formatting will not render correctly in older versions of Excel.

Is there a version of "Paste Values" for conditional formats that
applies the formatting directly to the cells? It would not longer keep
up with changes, but for presentation purposes it would work just

For example, I have a table of about 2200 rows by 50 columns. Going
down a particular column, most of the cells are zero, and the nonzero
values tend to occur in clumps. Using number formatting 0.0_);(0.0);;@
I blanked out the zero values. Using conditional formatting, I was
able to highlight the first cell of a cluster one color, the middle
cells a second color, and the last cell (before going back to zero) a
third color. I also have some conditional formatting that marks
certain rows, and lots of other stuff going on to help with data
visualization. In all there are 8 conditional formats, and it looks
just the way I want it to look.

I'd like to be able to save this as an Excel 2003 file and keep the
formatting. I know that Excel 2003 can't handle all these conditions,
but I would like a way of just applying the formatting-as-displayed to
the cells before saving. Something analogous to how "Paste Values"
will give you the value-as-displayed without the underlying formula.


Had several different types of conditional formatting in a sheet that had
been designed using Excel 2003 (*.xls). Converted the sheet to (*.xlsx)
format. I can successfully add and edit new conditional formatting to the
sheet, and when you save it, it appears to save all changes, but the
conditional format changes are not saved. Any ideas about what is going on


Ostate in Houston


i'm using the following code to make sure certain cells are filled in before the user can save the file. I'm also using conditional formatting on the sheet, is there a way to use the current code to ensure cell entry, as well as stopping the user from saving a file if a certain cell is a certain colour??


Current code:

Private Sub WorkBOOK_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Application.ScreenUpdating = False

Dim Rng As Range
Dim Cell As Range
Set Rng = Sheets("").Range("d3,e10")
Dim allIsWell As Boolean
allIsWell = True
For Each Cell In Rng
If IsEmpty(Cell.Value) Then
MsgBox "You must FIRST fill in cell " & Cell.Address(0, 0)
Application.Goto Cell
Cancel = True
allIsWell = False
End If

Hoping someone can help with this. I have created a simple conditional format whereby the text in one cell has white (invisible) text unless someone answers the question in another cell incorrectly, in which case the text turns red so the user can see it. This seems to work fine if I save the file as .xlsx and reopen in Excel 2007. However, if one of my teammates opens the file in Excel 2000 or 2003, the formatting doesn't work. I can look at the formatting rule and it appears like it should work, but it doesn't. I tried saving as Excel 97 - 2003 format, but then the formatting doesn't even work for me when I reopen in 2007. The weird thing is that if you open the file, see that the formatting isn't working, and then explicitely change the font in that cell to white (even though it already appears to be white), the formatting starts working.

I'm attaching a sample file and would appreciate any thoughts on how to get around this issue. (At least I think I attached the files?)



Hi all,

I'm currently working on a file which keeps track of a top 40-chartlist.
In cells E2-E41 I have the 40 hits of a particular week.
Through conditional formatting i've been able to highlight the new entries of that week
(yellow background, red cellcolour and bold font)
I've got a macro which saves my selection E2-E41 as a separate file.
The problem is that I want it to be saved with the conditional formatting mentioned above.
(Now my selection is saved with the default cell formatting)

Here's my code so far:

Application.ScreenUpdating = False
Dim MyCell As String
Dim MySaveRef As String
MyCell = Sheets("Inlezen nieuwe Top 40").Range("A49")
MySaveRef = "C:Test" & "week" & MyCell & ".xls"
Dim SourceBook As Workbook, DestBook As Workbook, DestSheet As Worksheet, _
ShCount As Integer, i As Integer
Set SourceBook = ThisWorkbook
Set DestBook = Workbooks.Add
ShCount = DestBook.Sheets.Count
Set DestSheet = DestBook.Worksheets.Add
SourceBook.Sheets("Inlezen nieuwe Top 40").Range("E2:E41").Copy
With DestSheet
.Range("A2").PasteSpecial Paste:=xlPasteValues
.Range("A2").PasteSpecial Paste:=xlPasteFormats
.Columns("A:E").ColumnWidth = 25
.Rows("2").RowHeight = 18
End With
Application.DisplayAlerts = False
For i = DestBook.Sheets.Count To DestBook.Sheets.Count - (ShCount - 1) Step -1
Next i
Application.DisplayAlerts = True
DestBook.SaveAs FileName:=MySaveRef
End Sub
I bet it's only one or two lines of extra code, but can't figure out what :S
Hope someone can help me out with this one

Thanx in advance

I frequently use conditional formatting to shade alternate rows.

I use
(1) Home>Conditional Formatting>Use a formula to determine which cells to format;
(2) Type in "=MOD(ROW(),2)=1";
(3) And apply a light gray shade so that it will work on the screen and when I print from my old B&W LaserJet.

Every time I do this I have to search for the correct formula, then go through the steps to create a new rule. I grant that this doesn't take a huge amount of time but it'd be much easier if I could some how save the rule for use in any workbook.

Is it possible to do so?

Thanks for any suggestions.

Why doesn't Excel allow me to save conditional formatting after about row 1000?

This is the ERROR MESSAGE: Excel couldn't save all data and formatting you
recently added to [filename]...

I have a spreadsheet that is 128 columns by 477 rows (A1 to DX 477) with 3 choice conditional formatting in about half of the cells and 2 choice conditional formatting in about half of the other cells. I needed to place conditional formatting on the last two columns. I cut and pasted the format. However, when I try to save the file it gives me an error message that says “Excel could not save all data and formatting you recently added to [Filename]” and the pasted formats are not saved. Is there a maximum number of cells that can use Conditional Formatting in a spreadsheet or given tab?

I have just used conditional formatting to implement a traffic lights to view performance.
I have altered some rules and created new rules, however only some the rules will save, the rest revert back to the old rules once i save & re-open the excel file.

Any ideas?

We have a spreadsheet that we update weekly. Before submitting it, we have to change the font color for new entries to red and the previous entries to black. Problem is some people forget. I'm trying to use conditional formatting to do this. I have a macro to get the last saved date, not sure if this is a good start since it doesn't seem to recalculate. Ideas?

Hi Everyone,
I have a spreadsheet in which I am giving the user a drop down list to select the type of information they want to enter. Once they chose a selection from the drop down list in Column A, I want to highlight the column header fields required to complete their requests. Because it will be distributed to many I did not want to use VBA & I am in desperate need of assistance to figure out how to use it with conditional formatting & formulas.

I have saved an example of what I need with the first row selected & the columns highlighted. The selections & headers assigned are on tab 2. There can be multiple selections, however all formats can be the same yellow & bold print.

Example DMT file 11.1.xlsx

I am trying to set the conditional formatting for a worksheet from VBA. I am trying to set it to the entire column so I am using to columns(x) as the "Range". My current code is like

    Worksheets(1).Columns(i).FormatConditions.Add Type:=xlExpression, Formula1:="=$a1" 
    Worksheets(1).Columns(i).FormatConditions(1).Borders.LineStyle = xlContinuous 
    Worksheets(1).Columns(i).FormatConditions(1).Borders.Weight = xlThin 
    Worksheets(1).Columns(i).FormatConditions(1).Borders.ColorIndex = 6 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem arises when the "activecell" on that worksheet (the cell that is selected on that sheet) is not the top then the relative rows get off.
If the selected cell is on row 5 then row 5's formula is $a1 & row 6 is $a2 and row 4 is $a65536. I am wanting to use this to modify (delete & then add) the conditional formatting on current workbook. I guess if I made sure A1 was selected on all the worksheets before I ran it that would be a workaround, but I was hoping I wouldn't have to do that so that it would be where it was when the user last used it. I could save the position & then put it back also. I was really just trying to find out if this was behaving as it should or if there was another way to program it.

Thanks for any help,

Hi, I am attempting to create a flat file database using excel and visual basic with the purpose of facilitating the management of a librarian's job. I basically have a rudimentary knowlege at my disposal of how to use excel and VB, so naturally, there are a few errors which have occured during the process of the creation of this database which I have no idea how to solve, as well as some ideas which i'd like to implement but I quite frankly do not understand how to go about doing so!

First of all, one of the errors which has occured is after I extracted the current system's database of students, year/class, ID, books and book Id. I've made a front end with VB which will allow me to input the name of the student and book which will in turn enter those in certain cells in my back end work sheet

    Cells(13, 6).Value = txtstudentname 
    Cells(17, 6).Value = txtbookname 
    Cells(12, 4).Value = txtstudentname 
    Cells(16, 4).Value = txtbookname 
    Unload frmStudentEntry 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then entered this forumula into the worksheet where the names have been inputted;


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i've named the tables with the student name, ID and Class "students" and the table with the book ID and book name "books" for easy reference.

the problem is that after I have entered the name and book name, the wrong value for the ID and class is picked up. Here is a sample of the back end with the inputted data;

Student Name		blah blah blah 
Student Year/Class		12C 
Student ID		21204 
Book Name		fiction 
Book ID		161 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
although the names match the ones on the databse worksheet, the actual values should be;

Student Name blah blah blah 
Student ID 12201 
Student Year/class 7C 
Book Name fiction 
Book ID 29 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I used to think that it might have been a problem with the character encoding or maybe enven because of double spacing or misplaced characters such as a random hyphen but it turns out that it is not the case. perhaps I should have saved the database as something more familiar to excel? I don't know...

I would also like to find out what formulas to use (probably conditional formatting) to make an automated notification of overdue books. What I mean is that I'd like to have a system where certain students will be noted down as having a book checked out, with a return date attached and then i'd like to have the system automatically check every day once its loaded to see which of those checked out books are overdue as well as all outstanding overdue books and then display those on a different sheet which will allow me to print it and mail merge it. I'd also need a way to take off from the system the functions which mark the students as having an overdue book.

also if possible, i'd need to know how to use the VB script like what I used to enter students into the work sheets to add students with overdue books manually into the overdue book system, but in such a way so that the newly entered books do not clash and erase the previously entered data (i.e. in seperate cells than the one previously entered)

Please help me, it would be much appreciated. Thank you in advance.


I have too many cell in my sheet that need conditional formatting (145638) and as such Excel won't save all of them. So I need a vba alternative.

I have 2 conditions. The first is to check for how many days are in a month. Here is the condition:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If this condition is met, I need to turn the affected cells, fonts and borders all white (to make it invisible). This condition comes into play when I save under a different name, which also sets the date of the workbook.

If this condition is not met, then we go to the second condition:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If this condition is met then this record (all 174 cells) needs to turn pink and turn the font to regular (not BOLD). Otherwise all is normal. This condition comes into play when data is changed on the link sheet (Input).

Is there some way to do this with vba?

Any help is appreciated.



Hi guys,

2 questions:

1) What would I write in the forumla section of the condition formatting to format cells equal to today's week number?

2) How could I have a sum of various cells (not next to each other) i.e. Y1,Y6,Y11 and Y16 (this is just an example, in reality I have alot more cells to refere to).
but ignoring all errors, at the moment if any one of them is an error it wont give me the sum of the others.


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