speed efficient VB worksheet change event code with large number of cells RRS feed

  • Question

  • I have a worksheet with formulas in approx 40,000 cells. The formulas result in either True or False and I need to monitor them all for a True value.

    Each of the cells is non-contiguous but set out in a regular grid over approx 20,000 columns by 10 rows.

    Does anyone have experience of VB coding a worksheet change event for such a large set of cells, who could pass on tips about speed efficient VB coding?

    I've considered a named range or union of several named ranges, and also coding a select case for groups of columns, amongst other possibilities, but am not sure which is most efficient.


    Regards, Tom

    Monday, August 27, 2012 8:18 AM

All replies

  • Have you though of conditional formatting which can highlight a cell if TRUE.

    But if you paste the code it will be much easy for us to help.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, August 27, 2012 9:47 AM
  • Asadulla,

    Thanks for replying.

    As my post indicates, I'm looking for VB code - CF is no use for me in this instance.

    As the post also indicates, I don't have have code (yet) - I'm in the planning stage.


    Regards, Tom

    Monday, August 27, 2012 9:59 AM