Link to home
Start Free TrialLog in
Avatar of NewToVBA
NewToVBAFlag for United States of America

asked on

Conditional Formatting in Excel pre 2007

Hello, I have a need to apply paint particular cells or rows upon found values in more than three categories per worksheet, so conditional formatting would not work for those using Excel 2003 or earlier version. I tried to use a private sub, it worked but eliminated the undo (ctr-Z) option when editing the worksheet. So I moved the sub to a module and invoke with a button, but then the advantage of dynamic feedback to worksheet user is gone.

Is there a better way to do it with no side effects like that?

Tks, N
-/-
Set Matrix= Range("J2", "J" & n)
For Each cell In Matrix
If cell.Value = "3" Then ' Color it red
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.ColorIndex = 3
ElseIf cell.Value = "2" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.ColorIndex = 6
ElseIf cell.Value = "1" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.ColorIndex = 4
Else ' Remove all color
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.ColorIndex = xlNone
End If

Next
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NewToVBA

ASKER

Hi Rorya,

I have no idea how to write an undo code. Can you assit with it please?

Tks,
N
We would need your full code as the Undo code has to undo whatever you just did.
Well, I guess, there is no solution other than just not using any such code. Then undoing is as good as not doing at all, no?

Tks
N