+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting doesn't show immediately (Excel 2013)

  1. #1
    Registered User
    Join Date
    01-08-2016
    Location
    Timisoara, Romania
    MS-Off Ver
    2013
    Posts
    12

    Conditional formatting doesn't show immediately (Excel 2013)

    Upon meeting the conditional formatting rule, the formatting change doesn't show for a long time.

    I know the rule works, but it requires me to scroll the cell out of view and scroll back up / page up & page down etc. for it to actually show. It's like it doesn't refresh the screen when it should.

    Searching for solutions online, I found some people mentioning "EnableFormatConditionsCalculation", which is set to "true" for me, by default, so that was a bust.

    The formula is really very simple - make text strikethrough if another cell has a certain value, so I really don't get why this happens.


    Any help would be appreciated.

    Thanks in advance.

  2. #2
    Registered User
    Join Date
    01-22-2016
    Location
    Toronto, Ontario, Canada
    MS-Off Ver
    MS 2013
    Posts
    2

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    Just something that happened to me earlier this month.

    Try looking at excel file >options>formulas and make sure your workbook formulas is set to automatic update.

    No idea why some workbooks suddenly change this setting, but it frustrated me for a couple of days before someone else suggested this.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    If this is a very large file and your computer's graphics isn't up to the task, it can take a while for the screen to update. greggie69's suggestion is valid too.

    If you have Conditional Formatting using complete column references, that can drastically affect performance negatively.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    01-08-2016
    Location
    Timisoara, Romania
    MS-Off Ver
    2013
    Posts
    12

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    Thank you for the replies.

    This is what I know so far:
    1. Workbook calculation has been and is set to automatic, although I checked it while searching for answers online.

    2. The file isn't that large or complex and my computer is powerful enough for Excel to not be a problem at almost any level of complexity, although I did wonder for a moment if it was a driver problem.

    3. My conditional formatting rule is basically (=J2="Incoming" then strikethrough text) and the same for another cell, just a different word. I made sure to keep it simple.

    edit:
    Apparently this is quite an old Excel bug (I've seen references to this starting with Excel 2007). People aren't entirely sure why it happens.


    So, trying the same conditional formatting on a new file results in the cells updating immediately, which is confusing, as the original file, as I stated before, isn't that complex (6 sheets with not that much data, formulas or code on each one).


    After searching online some more, I found a workaround here.

    Basically, all I needed to do is add this to the sheet's VBA:
    Please Login or Register  to view this content.
    Thankfully I didn't have other code running for that sheet.

    SMH
    Last edited by vitesse; 01-22-2016 at 06:19 PM.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    Where does Ctrl + End take you in relation to your actual data? If it is far beyond the active area, that may be the problem. To eliminate excess formatting and reset the end of data this utility from Microsoft will help. There is a Microsoft Add-in to remove excess formatting in Excel workbooks. It is available at http://www.microsoft.com/en-ca/downl....aspx?id=21649
    There should be a utility built into Excel 2013 that does this. Try that first.

  6. #6
    Registered User
    Join Date
    01-08-2016
    Location
    Timisoara, Romania
    MS-Off Ver
    2013
    Posts
    12

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    Apart from one sheet where it goes up to row 999 (but column T) - which is a data sheet that gets filled in time, all the other sheets are within reason (very close to the visible area).

    The most complex things I'm using are VLOOKUP to get data from the data sheet, some VBA code to correlate info between sheets and a very long formula someone made to spell out currency (Romanian currency which has different genders, so I can't use the simple dollar VBA code).

    I seriously can't believe this would be a problem. I mean I do much more complex stuff on a project I'm working on in Google Sheets and I doubt the allocated resources are higher than what I'm using locally.

    It's something to keep in mind though for future endeavors.


    edit:

    It's only a problem with strikethrough text. Everything else I tried works without any problems - background color, letter color, bold, italic, underline etc.

    I noticed that when I select a cell and create a new Conditional Formatting rule, the strikethrough option has the black square within the white square (partial select - image below).
    Attached Images Attached Images
    Last edited by vitesse; 01-22-2016 at 07:42 PM.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    I have just checked formatting in Conditional Formatting and my font formatting dialogue is identical to yours except the at the colour of Strikethrough is blue.

  8. #8
    Registered User
    Join Date
    01-08-2016
    Location
    Timisoara, Romania
    MS-Off Ver
    2013
    Posts
    12

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    It's weird. If I remember correctly, that's how it looks when part of the selection has an option set (like strikethrough), while the rest doesn't - which is why the square is neither empty nor checked. But I only selected one cell.

    In the meantime, I created a workaround with replacing words instead of using strikethrough. It's not exactly what I would have liked but it does the job.

    Thank you for all the help.

  9. #9
    Registered User
    Join Date
    12-16-2016
    Location
    Toronto, Canada
    MS-Off Ver
    Win 7
    Posts
    1

    Re: Conditional formatting doesn't show immediately (Excel 2013)

    The strikethrough with conditional formatting is still a bug in Excel 365 (v16). However, I have found that if I added a colour to the text as well as the strikethrough, it seems to update fine - fingers crossed

+ 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. Conditional Formatting in graphs for 2013 excel
    By mw2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-23-2015, 02:29 PM
  2. Replies: 2
    Last Post: 05-27-2014, 10:21 AM
  3. Extract cell value if it has conditional formatting color (Excel 2013)
    By Rickysnips in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2014, 07:46 PM
  4. [SOLVED] Conditional Formatting (in EXCEL 2013)
    By metsci in forum Excel General
    Replies: 2
    Last Post: 02-24-2014, 02:38 AM
  5. Excel 2013 : Conditional Formatting
    By cresh0r in forum Excel General
    Replies: 5
    Last Post: 01-04-2014, 11:05 PM
  6. Replies: 0
    Last Post: 09-20-2013, 11:15 AM
  7. Replies: 4
    Last Post: 08-21-2009, 04:04 AM

Tags for this Thread

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