+ Reply to Thread
Results 1 to 4 of 4

Big conditional formatting formula slowing down file, need help to rewrite

  1. #1
    Registered User
    Join Date
    03-25-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    2

    Big conditional formatting formula slowing down file, need help to rewrite

    Hi,

    I have attached a file with a "campaign tracker".
    campaign_tracker_example.xlsx

    Screenshot_042114_012108_PM.jpg

    Idea: After entering your campaign name, launch date and end date, it is meant to help display a calendar where these important dates are marked with conditional formatting in different colors.

    Problem: I need this calendar to be able to scale above 1000 rows, possibly 5000. When simply "filling down" with the below formula, the file becomes very sluggish.

    Request:
    1. Help me re-write the formula to an Array-Formula or similar to make the file fast and small-sized.
    2. Possibly incorporate the in-cell formula(creating number values) into ONLY conditional formatting to do the entire job of finding/checking/coloring cells.

    The calendar cells have a function checking for dates and entering a number:
    • If column date equals start date type "1"
    • If column date equals start date minus 3 days type "2"
    • If column date equals start date plus 1 type "3"
    • If column date equals end date type "4"
    • If column date equals end date plus 1 type "5"
    • If column date is between start and end dates enter "6"
    • If column date is between start and end dates enter "7" for every 7th day inbetween

    After this, conditional formatting applied to the area then checks for these numbers entered and adds background color to the cells.

    Looking forward to hear your ideas and suggestions on this! Many thanks in advance!
    /Henrik
    Last edited by henkisdabro; 04-21-2014 at 01:21 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Big conditional formatting formula slowing down file, need help to rewrite

    Hi and welcome ot the forum

    Re-writing your formula is unlikely to help. CF is a resource hog, and when you apply it to large ranges, it will start slowing things down for you
    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
    03-25-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Big conditional formatting formula slowing down file, need help to rewrite

    Quote Originally Posted by FDibbins View Post
    Hi and welcome ot the forum

    Re-writing your formula is unlikely to help. CF is a resource hog, and when you apply it to large ranges, it will start slowing things down for you
    Thanks for your welcoming! It is a fun and resourceful forum, i like reading it! Also thanks for your recommendation regarding CF. Would you have a solution on how to achieve something similar without killing the system?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Big conditional formatting formula slowing down file, need help to rewrite

    Only think I can think of is to use VBA for this (not my strong suite)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Many Combo boxes slowing down file
    By Commercial_Coder in forum Excel General
    Replies: 1
    Last Post: 12-07-2012, 07:35 PM
  2. Nested IF formula that can rewrite another cell
    By lizzyann in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2011, 09:07 PM
  3. Conditional Formatting Is Really Slowing Down My Computer, Any Suggestions?
    By Orangeworker in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-10-2010, 03:28 PM
  4. Conditional Format Slowing Scrolling
    By yorkshirewhite in forum Excel General
    Replies: 1
    Last Post: 07-18-2008, 08:13 AM
  5. [SOLVED] View zoom slowing opening of the file
    By ConnieM in forum Excel General
    Replies: 0
    Last Post: 07-27-2006, 06:10 PM

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