Very Slow Conditional Formatting In VBA

  • Hi guys, I have a conditional statement over thousands of cells....

    This is in a module:

    This is in a worksheet:

    1. Private Sub Worksheet_Calculate()
    2. Call colourcells
    3. End Sub

    As you can see, the range is L3:AI6722.... This goes VERY VERY slow, up to 50+ seconds :(

    There are a few problems with my current coding:
    1. Very slow
    2. It only updates when there is a calculation made (not when data is entered, which is what i want)

    Possible solutions for both my problems that i dont know how to do is:
    - Only check conditional formatting when data is entered into a cell that is right of a cell.

    1 2/2/06 ------- 2/2/06 ------ 2/2/06
    2 2/2/06 ------- 2/2/06 ------ 2/2/06
    3 2/2/06 ------- 2/2/06 ------ 2/2/06

    So all i really need to do is make the conditional formatting apply to columns L,N,P etc.... and for example only check the formatting for L1 when L1 and/or M1 is changed. (The data, not actually when calculations are made like my above solution)

    PLEASE PLEASE PLEASE help me, this is the last thing for my final solution. This has taken me a while and i just wanna get it over and done with :)