NewToVBA
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.C olorIndex = 3
ElseIf cell.Value = "2" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C olorIndex = 6
ElseIf cell.Value = "1" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C olorIndex = 4
Else ' Remove all color
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C olorIndex = xlNone
End If
Next
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.C
ElseIf cell.Value = "2" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C
ElseIf cell.Value = "1" Then ' Color it green
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C
Else ' Remove all color
Range("J" + CStr(cell.Row) + ":J" + CStr(cell.Row)).Interior.C
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We would need your full code as the Undo code has to undo whatever you just did.
ASKER
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
Tks
N
ASKER
I have no idea how to write an undo code. Can you assit with it please?
Tks,
N