jproffer
10-26-2008, 04:20 PM
I want to add a worksheet change event, but I want to make it optional to the user (It's an activecell highlighter, basically). I can make it optional with an IF statement:
If ActiveSheet.Range("IV65536").Value = "X" Then
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
Else
End If
End Sub
Two problems though:
1) An if statement won't work in this case because this is part of an add-in and I'd like to make this an option in the active workbook, whatever that may be.
2) I can't for the life of me, figure out how to change the code so that if I "run over" a cell that already has a color-fill other than yellow (or that is yellow for that matter), it will be restored to the original color when the cell is exited.
The only way I can see to solve problem one, is to write the code into the active workbook code module on the fly. Any other ideas? If not, how would one go about adding that code, by code?
Naturally the activeworkbook would change and I suppose I should remove the code when that happens (making the user "re-choose" that option each time they change workbooks).
Any thoughts or help is greatly appreciated. The second problem is sort of less important...but it's still a problem. I would imagine it would keep peace among users if the highlighter doesn't remove all their color formatting.http://www.mrexcel.com/forum/images/smilies/icon_smile.gif
Thanks in advance
If ActiveSheet.Range("IV65536").Value = "X" Then
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
Else
End If
End Sub
Two problems though:
1) An if statement won't work in this case because this is part of an add-in and I'd like to make this an option in the active workbook, whatever that may be.
2) I can't for the life of me, figure out how to change the code so that if I "run over" a cell that already has a color-fill other than yellow (or that is yellow for that matter), it will be restored to the original color when the cell is exited.
The only way I can see to solve problem one, is to write the code into the active workbook code module on the fly. Any other ideas? If not, how would one go about adding that code, by code?
Naturally the activeworkbook would change and I suppose I should remove the code when that happens (making the user "re-choose" that option each time they change workbooks).
Any thoughts or help is greatly appreciated. The second problem is sort of less important...but it's still a problem. I would imagine it would keep peace among users if the highlighter doesn't remove all their color formatting.http://www.mrexcel.com/forum/images/smilies/icon_smile.gif
Thanks in advance