VBA Conditional Formatting - code efficiency

[Vandermiet]

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

[snb]

I think you don't need
- a backup worksheet
- a macro to check all cells in the range.
- any formula
- any formatcondition

You could simply use the worksheet_change event to accomplish this

Private Sub Worksheet_Change(ByVal Target As Range)

target.borders.Color = -16776961
end sub

[Vandermiet]

Dear snb,

Thanks for the reply, I'll definitely try utilizing it. But I have some concerns. I am not able to put any code directly into the worksheet object as the whole file is generated from an external system which forces its own code into the worksheets. So this may be a little tricky as all I can do is supply my users with an .xlam file. I am not sure whether On Change event can be executed from a standalone module.

Additionally, as one of the next steps in the process I need to copy only the cells with values altered. I created a sub to do it basing on the backup sheet. Do you know if such sub would be possible to create basing on the change event? I don't want to utilize the borders color as the users are not prohibited from applying their own formats here.

[snb]

Where did you put the code you posted ?

It's definitely possible to add the worksheet_change event code after the import of the file.
It's not clear to me what you want to accomplish: to indicate to the user what has been modified, or to introduce a marker for changes that can be used further in the program ?
Please describe the whole process, because there are many ways to accomplish this.

[Vandermiet]

Actually, you inspired me for some further investigation and I think I found a solution based exactly on your idea.

Anyway - I am aiming at both effects - notifying the user and marking cells with changed values.

Thanks for your time and help! Highly appreciated!