+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting

  1. #1
    Registered User
    Join Date
    03-23-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Conditional Formatting

    Hi All,

    I have applied conditional formatting to a range of cells(For example from A1 to A10). If sum of this range is not equal to 100, then the specified range should be displayed in red color, otherwise in blue color. But when I change any of the cells from this range (which results the sum > 100), then that particular cell alone is getting changed to red color. If I scroll down or scroll right then remaining cells from that range is changed to red.

    Please let me know why the worksheet is not getting refreshed properly?

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    Re: Conditional Formatting

    make sure your worksheet is on automatic recalculation.
    Go to Tools\Options\Calculation and set it for automatic.
    modytrane

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Conditional Formatting

    What is the actual conditional formula you are using...

    should be something like: =Sum($A$1:$A$10)>100

    P.s. Also, your title should be a bit more descriptive of the problem.. the current title is too vague.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    03-23-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional Formatting

    Thanks for the update.

    The excel sheet which I use is very complex and contains many worksheets with so many formulas. The excel sheet becomes non responsive when I toggle between say data validation controls or perfom some other functionality like button click. To handle this I have changed the calculation mode to manual.

    In this scenario how can I handle conditional formatting problem?

    Thanks

  5. #5
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    Re: Conditional Formatting

    You have to make that part of the sheet calculate automatically, if you want to update conditional formatting. You may not want the entire workbook calculate automatically. I don't know if there is a way to make just one sheet calcualte automatically. Someone else might an answer for that.

    However, I am pasting some code below that allows you to calculate only part of a sheet. You would have to put this in the worksheet under change_event or assign it as macro to a button. That way; only that portion of the worksheet will calculate very time you make a change.

    If you attach a sample workbook, we can show you how to make it work.



    HTML Code: 
    Sub CalculateRange()
    
        Dim Rng As Range
          
        ActiveSheet.Unprotect
        Set Rng = ActiveSheet.Range("A:BZ") ' assign range of cells to calculate.
        Rng.Calculate
        ActiveSheet.Protect
    end sub

    modytrane

  6. #6
    Registered User
    Join Date
    03-23-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional Formatting

    I have added following line of code in the worksheet_change event and it worked.

    Please Login or Register  to view this content.

    Thanks for your inputs

+ 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