Hi guys, I have a conditional statement over thousands of cells....
This is in a module:
- Sub colourcells()
- Dim icolor As Integer, r As Range
- For Each r In Range("L3:AI6722")
- If IsDate(r.Value) Then
- If Date < r.Value Then
- icolor = 43 'green
- ElseIf Date > DateAdd("m", 1, r.Value) Then
- icolor = 3 'red
- Else
- icolor = 45 'orange
- End If
- Else
- icolor = 2 ' white
- End If
- If Not IsEmpty(r.Offset(, 1)) Then
- icolor = 2 ' white
- End If
- r.Interior.ColorIndex = icolor
- Next
- End Sub
This is in a worksheet:
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