Very Slow Conditional Formatting In VBA

rajt85 · May 22nd 2010

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

This is in a module:

Code

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

Display More

This is in a worksheet:

Code

Private Sub Worksheet_Calculate()
Call colourcells
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

Share