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,280
    Thanks
    3
    Thanked 191 Times in 177 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
    988
    Thanks
    56
    Thanked 105 Times in 90 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
    988
    Thanks
    56
    Thanked 105 Times in 90 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,280
    Thanks
    3
    Thanked 191 Times in 177 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
  •