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?

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

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

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

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