Conditional Formatting in Excel 2007

[hjgoldstein]

I have an interesting one for you.

I produce a daily report in the form of a pivot table part of which has Conditionally Formatted Colour Scales set across ten columns. The code for the Conditional Formatting is:

    Range("D9:M" & lngLastRowPivot).Select
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With

For the last two days the conditional formatting has not displayed on the report. There is no apparent difference between the previous (working data) and the current (not working) data other than the number of records the pivot table is based on is >150 for the last two days.

If I manually select <=100 rows in the pivot (there are >300 including Row Labels (and sub-labels)) I can use the conditonal formatting but when I select >=101 I can't.

What is even stranger is I can copy the entire pivot data area (excluding headers and filters) into a new sheet the conditional formatting is copied in correctly which tells me that it is just the display which is causing the fault.

Any ideas, genii?

Aspiring to mediocrity since 1957

 

[hjgoldstein]

I have now found that if I un-check one record in the filter or even if I collapse one record to main level only the Colour Scale appears as it should. I am no less confused but I put this to the collective for enlightenment.

Aspiring to mediocrity since 1957

 

[byundt]

This is the type of problem that would be helpful if you answered a few questions:
1. Which version of Excel are you using? Bugs are often specific to a certain version of Excel.
2. Could you post a sample file that demonstrates the problem? Use fake data if necessary.
3. Is the code being run on a workbook that has never been formatted before? If not, is there code that deletes any previous Conditional Formatting before the section of code in question runs?

If I were stepping through the macro one statement at a time using F8, I'd be very interested in the value of lngLastRowPivot. It may be that it isn't being set correctly.

Brad

 

[hjgoldstein]

Thanks for responding. To answer your points:

1. Excel 2007 SP3;
2. Sorry, that would be instant dismissal;
3. This code has been running for a number of months and this issue only arose on Friday last;
4. I checked this morning and the variable is setting correctly.

I'm afraid I can't give any more at this point.

Aspiring to mediocrity since 1957

 

[hjgoldstein]

Well, today it was fine. Never mind.

Aspiring to mediocrity since 1957

 

[byundt]

I'm glad that Excel is behaving better today.

Although Excel 2007 does have a number of reported issues with Conditional Formatting, I didn't see any problems like yours when I searched.

Brad

 

[hjgoldstein]

I have an idea what may have been causing it but won't know for sure until the next time I run the report on Monday. I'll post here if I find anything.

Thanks for your input.

Aspiring to mediocrity since 1957

 

[hjgoldstein]

Well it was all better for a while but now it is playing up again.

Now I can manually set the colour scale conditional formatting to 300 rows and then the remainder separately. OK as far as it goes but not ideal.

I am now wondering if there is a problem with memory. I work on a thin-client connection and therefore there is no local processing or RAM. I'll try something out and see if that is possibly the cause.

Further bulletins as events warrant!

Aspiring to mediocrity since 1957