• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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