Vandermiet
08-31-2012, 01:49 AM
Dear All,
I have a question regarding code efficiency related to setting conditional formatting on multiple cells. Here is the case description:
- I want to highlight the cell once user changes its value
- To do this I am creating a copy of the sheet and hide it. The copied sheet is called "FlowBackup", the one where changes are tracked is called "Flow"
- Then using vba (code below) I am applying conditional formatting basing on a simple principle: if Flow!Range.Value<>FlowBackup!Range.Value then apply chosen formatting
PRNG stands for range to be processed
For Each RNG In PRNG
Vaddress = RNG.Address
RNG.FormatConditions.Vaddress Type:=xlExpression, Formula1:= _
"=" & Vaddress & "<>FlowBACKUP!" & Vaddress
With RNG.FormatConditions(1)
.Borders(xlLeft).Color = -16776961
.Borders(xlRight).Color = -16776961
.Borders(xlTop).Color = -16776961
.Borders(xlBottom).Color = -16776961
End With
Next RNG
Everything works great, with one exception. The PRNG contains around 20 000 cells and the whole operation takes approximately 3 minutes.
Would anybody have any idea how to optimize such code? I know that limiting the number of operations - i.e. choosing the RNG.Interior.Color would be more efficient (less operations), but I need to use the borders...
I have a question regarding code efficiency related to setting conditional formatting on multiple cells. Here is the case description:
- I want to highlight the cell once user changes its value
- To do this I am creating a copy of the sheet and hide it. The copied sheet is called "FlowBackup", the one where changes are tracked is called "Flow"
- Then using vba (code below) I am applying conditional formatting basing on a simple principle: if Flow!Range.Value<>FlowBackup!Range.Value then apply chosen formatting
PRNG stands for range to be processed
For Each RNG In PRNG
Vaddress = RNG.Address
RNG.FormatConditions.Vaddress Type:=xlExpression, Formula1:= _
"=" & Vaddress & "<>FlowBACKUP!" & Vaddress
With RNG.FormatConditions(1)
.Borders(xlLeft).Color = -16776961
.Borders(xlRight).Color = -16776961
.Borders(xlTop).Color = -16776961
.Borders(xlBottom).Color = -16776961
End With
Next RNG
Everything works great, with one exception. The PRNG contains around 20 000 cells and the whole operation takes approximately 3 minutes.
Would anybody have any idea how to optimize such code? I know that limiting the number of operations - i.e. choosing the RNG.Interior.Color would be more efficient (less operations), but I need to use the borders...