Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I've used the event "Worksheet_Change(ByVal Target As Range)" to do conditional formatting, as 2003 is limited to ony 3 conditions. This is working well if I change the values of each cell in the range.

    I have changed the cells to references, so the cell values change, but this is not triggering the Worksheet_Change event.

    Any ideas How I can get the formatting to work for these referenced cells.

    Thanks

    - Moz

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,284
    Thanks
    3
    Thanked 193 Times in 179 Posts
    You either need to monitor the input cells for those formulas, or you'll need to use the worksheet's Calculate event and check each cell of interest (calculate doesn't tell you what changed).
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,004
    Thanks
    59
    Thanked 109 Times in 94 Posts
    I had exactly the same issue, which I solved as follows:

    Instead of using a formula in the cell, I used a macro to write the same result into the cell. This action then triggers the Worksheet_Change(ByVal Target As Range) event.

    I though this was going to be laborious but, once I'd designed the workbook along these lines it turned out to be easier (and better documented) than using formulae.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help,

    I ended up copying the code from the "Worksheet_Change(ByVal Target As Range)", and tweaking it to run in a macro, and before printing the sheet make sure that the user runs the macro which then checked the contents of each cell and changed the interior fill accordingly.

    Thanks again for your help.

    Sudden thought, can you get a macro to automatically run before printing?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,004
    Thanks
    59
    Thanked 109 Times in 94 Posts
    It is hard to "intercept" the Print command, but why don't you just add "print" to your re-formatting macro and provide an alternative Print button with the new macro assigned to it ? Possibly remove the default Print button too. And/or privide a menu item with your new "Format and Print" command ?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,284
    Thanks
    3
    Thanked 193 Times in 179 Posts
    You can either use the Workbook_BeforePrint event or you can trap the clicking of the print/print preview buttons. The former is easier - the code needs to be in the ThisWorkbook module.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good idea Rory,

    Thanks for your help.

    - Moz

Posting Permissions

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