Excel (2010) conditional formatting doesn't refresh

[Zetec-S]

Hi all, looking for some wisdom with regards to conditional formatting in Excel 2010. I have a fairly large (2000+ lines) worksheet with a conditional format applied (shading and borders), but annoyingly when I scroll up or down the formatting does not update automatically. Instead I have to click into another worksheet and then back, or hit F9 (auto-calculate is already on) or edit (F2 then enter) a cell for the formatting to apply.

A search online suggested adding the following VBA code but it doesn't seem to have made a difference.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

Any suggestions, or is this one of those annoying excel quirks which can't be avoided?

[mcflurry]

Is the box ticked under file / options / formulas / workbook calculations?

[Zetec-S]

Yes, auto-calculate is ticked.

[louiebaby]

In your little bit of script, replace the middle line with Calculate.

Let us know how you get on.

[hyphen]

For conditional formatting, I don't think any VBA should be needed.

We can mess about going back and forth, or you can post up a link to the spreadsheet (as a xlsx so you don't infect us with your dodgy coding ). replace any work data with dummy data if needed.

p.s. 2000 rows ain't large

[Zetec-S]

Thanks for the replies. I've tried replacing with calculate and that still doesn't work. I'll try to get an example put together and a link from home, as doing anything like that these days at work is a big no-no.

In the meantime, this is what I'm trying to do. The conditional formatting means if the word "Total" appears in that first column then the cells in that row will be made bold, given a dotted top & bottom border, and shaded grey.

The bold text and borders works fine, but per the example below the grey shading doesn't appear straight away when I scroll up or down. Even though auto-calculate is on, I have to hit F9 for the grey to appear on the page (at the top the grey appears because I had already F9'd before scrolling down to these rows).

hyphen said:

p.s. 2000 rows ain't large

Yes, true, but the data which sits behind this worksheet is nearly 100,000 lines and we're only halfway through the year

[mmm-five]

I've seen similar issues with random workbooks I've used. Doesn't seem to be any rhyme or reason why some do it and some don't.

Sometimes just duplicating the worksheet and deleting the original fixes it, sometimes I have to start almost from scratch, sometimes I just have to clear the conditional formatting from all but the top row and then copy & paste the formatting back to the others.

BTW, this is for sheets with 600 to 113,000 rows (4mb to to just under 1gb) - so the size isn't the determining factor either.

I've even tried turning off hardware acceleration, and again sometimes it helps, sometimes it doesn't.