• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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
0
NewToVBA
Asked:
NewToVBA
  • 2
  • 2
2 Solutions
 
Rory ArchibaldCommented:
No. If you use code you lose the Undo stack unless you create your own Undo code.
0
 
rspahitzCommented:
If you only have four choices, use one as your default (starting) choice then use conditional formatting for the other 3 choices.
0
 
NewToVBAAuthor Commented:
Hi Rorya,

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

Tks,
N
0
 
Rory ArchibaldCommented:
We would need your full code as the Undo code has to undo whatever you just did.
0
 
NewToVBAAuthor Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now