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:

    Code
    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.


    e.g.
    ----L------M------N-----O-----P-----Q-----.........
    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 :)


    Cheers,
    :music:
    Raj