+ Reply to Thread
Results 1 to 4 of 4

1 workbook opens slow due to conditional formatting - need a better way or to fix

  1. #1
    Registered User
    Join Date
    04-13-2012
    Location
    Corona, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    1 workbook opens slow due to conditional formatting - need a better way or to fix

    Hello everyone

    I am self-taught at Excel and my learning tools have been trial and error along with internet searches on how to do something in particular. This is the first time I cannot find an answer that helps me so joined this community to see if someone knows a better way or how to get this file to open in a reasonable time frame.

    I am using Excel 2010, all updated. I am trying to make a training matrix. I have several tabs and at the top running from left to right is a document number and doc title. On the left, running down are names of people. Each tab is a department so everyone in the department is listed. Let's say Employee X was trained on document 1, I would find the cell that matches and put in the date they were trained. That is all simple and straight forward. What I need the file to do is when it is within 30 days of one year of that date, turn the cell yellow. One year and one days past the year, turn the cell red. I got that figured out using conditional formatting.

    The problem is, each cell has to have that CF in it. I did a count of cells, total for all tabs and have 11,125 cells with 2 conditional formats in them. Once I started to get a lot of these cells done, the workbook started taking longer and longer to open. The file is just under 5 MB, so still small. It take up to 3 minutes to open now. I had someone else open the file and it took as long. I sent it home and tried on my PC, which just upgraded to an 8 core processor, SSD HD, and all of that and still took a while. I believe it is because it is recalculating each cell when opening.

    I disabled auto-recalculations but made no difference. I believe I read that it doesn't shut off CF anyway. I have spent some time making this file but if opening it takes this long, then others probably won't use it and they will need to. I don't know if there is another way to have the cells highlight based on dates past other than maybe a macro, which I don't know macros. Is there an easier way to have them highlight than setting up CF in each and every cell? I tried to do a grouping of cells but the highlighting only works based off of the first cell so that doesn't work.

    I need either a different way so it speeds up the opening issue. It will have to auto calculate so when another manager opens the file, he/she can just look for the yellow and red cells so they know who need the yearly training for that month, plus which document/s to train.

    Any and all help will be appreciated, especially after spending a week on this thing. Thank you very much!!!

    Oh, forgot to mention that when I copy a cluster of cells and paste, it will sometimes lock up the workbook. Even happens if I copy one cell, then paste it 5, 6 or 7 times. This work computer is new and judging by the specs, it should open just fine.

    Thank you again!!!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: 1 workbook opens slow due to conditional formatting - need a better way or to fix

    try and restrict your CF to the minimum possible, it takes up a LOT of resources!! also, check your CF cells to make sure the CF rules have not been repeated twice or even 3 or more times. I use 2007, and if you copy a cell with CF, to another cell with CF, it doubles up the CF rules, even if they are identical...not sure if 2010 has the same problem or not.

    If you only use CF on certain rows, make sure the CF is ONLY applied to that range, and remove it from any other ranges
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-13-2012
    Location
    Corona, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: 1 workbook opens slow due to conditional formatting - need a better way or to fix

    Thank you, FDibbons. Unfortunately, I already have it limited to the minimum. In 2010 when a cell with a CF is copied from a column to another, you have to go back in and adjust the lookup cell. If copied down row to row, it will auto adjust. I kept double checking to ensure the CF was correct and have not seen a single issue of them getting doubled up. The range for the CF is the cell the information is in so no range issues. It all works as designed, just a bear to open!

  4. #4
    Registered User
    Join Date
    04-13-2012
    Location
    Corona, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: 1 workbook opens slow due to conditional formatting - need a better way or to fix

    Thank you to everyone who has read this post; I didn't think this would be one that could be solved. For now, I will just have to live with it until a different option arises. Thank you again. I will try and mark this as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1