Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Britain
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    When styles get overridden (2000 SR-1)

    To start with a quick instance of the problem:

    1) Open a new blank worksheet.
    2) Select a rectangular block of cells, go to Format|Cells|Patterns and change the interior colour.
    3) Keeping the selection, go to Format|Cells|Patterns and change the colour back to what it originally was.
    4) Now go to Format|Style|Modify|Patterns and change the interior colour.

    The block which you changed and then changed back doesn't change, though the rest of the sheet does. The same seems to go for borders, number formats, alignment etc - and the same cell may change with the style for some of these but not others. However, I can't find any property in VBA that will allow me to tell whether a cell is going to exhibit this behaviour in any of these cases.

    So, I need to tidy up six largish sheets (about 2000 rows by 150 columns each), amended and reamended by several people over the last six or seven years. At one time or another, while more than 95% of the cells on the sheets have the styles they are supposed to have, somewhere around half the cells seem to have override values for one property or another, usually though not always the same as the default for the underlying style - someone had a good temporary reason for, say, highlighting a row or column and changed it back later. Occasionally, to complicate matters, the reason still applies - so I can't just run a procedure to amend aberrant values back to the default for the style automatically.

    Any advice?

    Peter Wilkinson

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When styles get overridden (2000 SR-1)

    In order ro return to your default Style, what you need to do is apply a different Style (which affects the amended aspect - borders, pattern, whatever) to all the Cells in question and _then_ change back to your original Style.

    As to your other point, it sounds as if you need to add a few custom Styles.

    Putting this together, as long as you can identify where (in general) your Styles need to be applied, you need to cycle through each block, apply a third (hideous) style, which is highly unlikely to have been used, and then change the block back into the target Style. HTH
    Gre

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Britain
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When styles get overridden (2000 SR-1)

    It helps a bit - but a lot of the problem is going to be in the "as long as you can identify..." clause. Taken as a local problem, this isn't hard for me to do, at least for two of the three already-existing custom styles (I've got no idea how, where in the six sheets or even whether the third style is being used - though I probably once did) and for a few other possible new styles.

    In general, if a cell is in both an appropriate row and an appropriate column, it should be in one of the two existing custom styles. If either the row or the column is not appropriate, it should not.

    But while the appropriate columns (about 100 of them - too many all to be seen on screen at one time) are contiguous, the rows are far from being so - short data blocks (one custom style), summary lines (the other style), space between blocks (currently normal style). The two styles are similar enough in practice that it's usually easier to tell which of the two a cell should be in that what it actually is in.

    Also, cells with visibly aberrant formatting are usually that way for a reason - to flag up data that needs checking or otherwise to indicate where data needs to be entered. The trouble with this is, of course, once the immediate need is over, the formatting is generally made invisibly aberrant - which, along with cells in the wrong style that have been patched to look as if they are in the right style, is probably the core of my immediate problem.

    So I get the feeling your suggestion should work - but unless there are some shortcuts (I may come up with one or two of my own, but that'll probably not be enough - suggestions eagerly awaited), it's likely to take a horribly long time and even then, unless I can stop colleagues from continuing with quick'n'dirty formatting solutions to problems rather than using existing and new styles, need redoing completely within a year or so.

    Peter Wilkinson

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When styles get overridden (2000 SR-1)

    At a guess, there should be some repeating text (such as "Total" adjacent to the summary lines) that can flag up where you need to apply the other styles. You may well be able to focus your searches for these strings so that you don't have to loop through each entire sheet.

    Fully integrated styles tend to have nowhere near the same weight in Excel as they do in Word. Something you may wish to look at going forward is some kind of Custom Review Toolbar which applies a Follow Up style and an Enter Here style. Naturally, this would require some user training to implement, and the necessary amenability.

    It still seems to me relatively reasonable that - once you've developed a way of flagging up your minor Styles (see above) - you could write a macro (based on relative references) to cycle through all the sheets are re-apply the target Styles. Having done that once, you would probably only need a minor update of the code when it came time to re-refresh in 12-15 months time. HTH
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •