Weird formatting issue

[AOB]

I have created a workbook that pulls information from Active Directory and presents it on a tab (arbitrary number of rows depending on the criteria etc.) Built it years ago, been tweaking it here and there over time but by and large still the same.

Recently, after one such tweak (which should have no bearing on this particular problem), I've noticed a very strange issue with the formatting on the resultant sheet.

For some reason, once the macro has completed, the cell contents have been shifted down for some reason, such that only the top of the text is visible, and the rest is obscured behind the cell below. The cell contents also appear to get "stretched" horizontally. But not consistently - the problem seems to exacerbate as you move left-to-right or top-to-bottom across the sheet.

I can resize the rows to see the missing text, but the weird top-margin-like behaviour still persists (an autofit reduces the rows to the correct, minimal size but the obscuring of the cell contents persists)

Weirder still - this behaviour seems to differ depending on where I open the workbook from? So if I save the exact same workbook to a number of different locations, the formatting problem doesn't present itself consistently when I open and run the code from each of those locations (e.g. if I run it from my local C: drive, there's no issue; if I run it from one network share, the issue presents itself; if I run it from a different network share, again, no issue)

Also - I have about 18-20 conditional formatting rules (applied dynamically via the code) - nothing particularly resource-intensive and the same rules have been in place since the beginning - but sometimes (and, again, there's no obvious consistency to this), the cells to which one rule applies, appear fine, but other cells to which other rules apply, suffer from this weird "shunting" problem. The priority of the rules doesn't seem to have any impact. If I delete a rule for a set of cells suffering from the "shunting", the now-unformatted cells return to normal. If I then re-introduce the rule, it formats everything perfectly (but only for those cells impacted by the rule - other cells covered by different rules, still have the "shunting") So in order to fix it, I would have to manually go through each of the rules, delete them and restore them again, to get rid of the formatting problem. Not ideal (the whole point of this is to automate the whole thing start-to-finish)

It's so bizarre and I can't figure out why this might be happening or, more importantly, how I can prevent it happening.

Has anybody else come across this and found a solution?

 

[rlv01]

Can you at least isolate it to something to do with conditional formatting? In other words, if you deleted all conditional formatting rules and save the file as a test version, does the weird sheet formatting behavior go away?

 

[AOB]

Can you at least isolate it to something to do with conditional formatting? In other words, if you deleted all conditional formatting rules and save the file as a test version, does the weird sheet formatting behavior go away?

Oh it definitely seems to be related to conditional formatting - as soon as I delete all the CF rules, the problem goes away, even without saving it.

And I can then manually add the exact same rules back in and they apply perfectly with no issue.

 

[rlv01]

Well, it is probably not all the rules. Have you tried deleting one by one until the culprit reveals itself? Or if there are a lot of rule then delete half then test - rinse and repeat.

The other thing that can happen with CF rules is that if you are doing a lot of copy & pasting rule fragmentation can occur and the number of rules grows. Any chance that is happening?

 

[AOB]

Well, it is probably not all the rules. Have you tried deleting one by one until the culprit reveals itself? Or if there are a lot of rule then delete half then test - rinse and repeat.

The other thing that can happen with CF rules is that if you are doing a lot of copy & pasting rule fragmentation can occur and the number of rules grows. Any chance that is happening?

The rules don't overlap in terms of the ranges they are applied to so as I delete each rule (and thus remove any formatting applied) the cell contents appear correctly again. So it's not one specific rule that's messing up all the others - any rules that result in this behaviour, correct themselves as soon as I delete them (irrespective of the order in which I delete them) But if I then add the exact same rules back manually, they apply perfectly.

Also, there's no copy/pasting going on - the macro pulls data and places it on the sheet unformatted. The conditional formatting is the final step in the process and is applied to data that's already there.

 

[rlv01]

Built it years ago, been tweaking it here and there over time but by and large still the same.

Have you ever run a code cleaner on it? Macro-heavy workbooks that have continual work/updates made to them have a way of going 'wonky' unless you do this periodically.

FWIW, I have a couple of large workbooks that have many, many CF rules. In order to keep it functioning smoothly I eventually had to write a 'refresh' macro that deletes all the rules, then re-adds them to the relevant ranges. I call the macro from the workbook open event so that the rules are refreshed periodically.

 

[AOB]

This particular workbook is designed to build itself from scratch on each iteration. It doesn't retain any data (and by extension, formatting) between iterations, even if you save it (I actually have code on both the open and close events to purge any pre-existing data so that it always opens "clean") It's designed to just provide snapshot data that is effectively out of date as soon as it's run so there's no value in keeping data from one run to the next (you don't want to)

So all the CF rules are being generated on a brand new sheet at runtime - there are no pre-existing data or formats to carry across!

FWIW I did run the Inquire > Clean Excess Cell Formatting function on it, both before and after a "run" - it didn't pick up on anything excessive and didn't solve the problem.

Only thing I can think of is to separate out the CF rules piece into a separate function and run it independently after the data query / enrichment has completed. But I can't see why that would make any difference to be quite honest...

 

[rlv01]

Only thing I can think of is to separate out the CF rules piece into a separate function and run it independently after the data query / enrichment has completed.

I don't think you have much to lose by giving it a try. If it works, great. If not, you have another data point to add to the puzzle.

But I can't see why that would make any difference to be quite honest...

Perhaps not, but speaking for myself here, when diagnosing stubborn oddball problems I find that I have to set aside my "I can't see why that would make any difference" mindset as it gets in the way of troubleshooting. Good luck.

 

[AOB]

I don't think you have much to lose by giving it a try. If it works, great. If not, you have another data point to add to the puzzle.


Perhaps not, but speaking for myself here, when diagnosing stubborn oddball problems I find that I have to set aside my "I can't see why that would make any difference" mindset as it gets in the way of troubleshooting. Good luck.

Agreed, and it is often difficult to separate what "should" be happening from what "is" happening and adopt that mindset!

I seem to have gotten around the problem by separating the section of the code that applies the CF, from the rest of the function.

What I suspect is causing the issue - although I can't explain why that might be with any degree of certainty - is the disabling of certain Application attributes (namely, again I suspect, ScreenUpdating, although it could be something else...)

As a general rule, but particularly with anything "heavy" in terms of inserting data and/or formatting, I start things off by turning off ScreenUpdating, DisplayAlerts etc., and then switch them back on again at the very end (ensuring that this happens even if there is an error thrown mid-run)

Public Sub RunReport()

    On Error GoTo ErrorHandler
    
    ' Switch off screen updating, alerts, events; ensure status bar is displayed
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .DisplayStatusBar = True
        .EnableEvents = False
    End With

    .....

Exit_RunReport:
    On Error Resume Next
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = False
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
    Exit Sub

ErrorHandler:
    Resume Exit_RunReport
    
End Sub

So what I suspect is happening, is that all the CF is being applied while the ScreenUpdating is disabled, and then when it is suddenly re-enabled at the end, there is some kind of resource issue in the painting of the window that results in the weird formatting appearance. My reasoning here is that the rules can be purged and then manually added back one at a time and they perform perfectly. So it isn't a problem with the rules themselves, but more the "sudden" application of the rules that Excel is, for some reason, struggling with.

So - I took the CF piece out completely and created a separate function called "FormatResults" to apply the CF which I don't call until after all the Application attributes have been restored :

Public Sub RunReport()

    On Error GoTo ErrorHandler
    
    ' Switch off screen updating, alerts, events; ensure status bar is displayed
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .DisplayStatusBar = True
        .EnableEvents = False
    End With

    .....

Exit_RunReport:
    On Error Resume Next
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = False
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
    FormatResults            ' <<<<< Call the function to apply the CF here
    Exit Sub

ErrorHandler:
    Resume Exit_RunReport
    
End Sub

This seems to fix the problem; I've run this on multiple devices, and from multiple locations (local, network etc.) and none of them are showing the weird botched formatting problem any more.

(I've also taken some ActiveWindow manipulation that was within the main function and moved it to this separate function as well - that could also be the cause as I've historically found modifying ActiveWindow properties to be a bit sensitive at times - just if anybody else is having this problem and what I've suggested above doesn't resolve it, worth checking to see if there is similar code somewhere that could be repositioned...)

With shtOutput
    ' Freeze header row & columns and remove gridlines
    .Activate
    .Range("D2").Select
    With ActiveWindow
        .FreezePanes = False
        .ScrollRow = 1
        .ScrollColumn = 1
        .FreezePanes = True
        .DisplayGridlines = False
    End With
    .Range("A1").Select
End With

Anyway - looks like the problem is solved now, hope this saves somebody else some time (or at least hair loss from head-scratching!) - if anybody wants to chime in on the philosophy or reasoning, I would be very interested!