sheetchange event macro.. what am I doing wrong?

[Joshua.Buss]

hi..

i'm just tryin to make it so that when the user inserts a 'g', 'r', or
'y' into a cell, the font for that cell changes color appropriately..
this looks fine to me but nothing happens...

---------------------
Option Compare Text

Private Sub Workbook_SheetChange(ByVal sheet As Object, ByVal cell As
Range)

If cell.Value Like "*g*" Then
cell.Font.ColorIndex = 4
ElseIf cell.Value Like "*r*" Then
cell.Font.ColorIndex = 3
ElseIf cell.Value Like "*y*" Then
cell.Font.ColorIndex = 2
End If

End Sub
---------------------


also, how do i create a signature so that i can make this a trusted
macro so i don't have to change the security level in excel just to
make this macro runnable? when i go to tools - digital signature -
choose - there are no options available :/

 

[Earl Kiosterud]

Joshua,

This should work if you put it in module ThisWorkbook. It will fire on a
change to any cell in any sheet. If you want it to work in one sheet, it
needs to be put in the module for that sheet, and must be changed to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value Like "*g*" Then
Target.Font.ColorIndex = 4
ElseIf Target.Value Like "*r*" Then
Target.Font.ColorIndex = 3
ElseIf Target.Value Like "*y*" Then
Target.Font.ColorIndex = 2
End If
End Sub

This is because there is no Cell object dimmed in the Worksheet_Change
procedure call. Use Target instead.

Also, do you want to restrict this to certain columns or anything like that?
Post back.

You could also use conditional formatting, since you have only three
conditions, and skip the vba.

 

[Tamale]

i couldn't find any way to use wild cards in conditional formatting for
'strings' or characters; i need it to change color if you enter just
"g" and also if you enter "grass"..

your fix makes it work fine.. thanks.

yes, actually i do need to restrict its usable places. what do you
suggest for that?

also, how do i get this macro digitally signed so my co-workers don't
have to lower their security level to use it?

 

[Tamale]

also, i'm getting the error "type mismatch" and it's pointing to this
line of code:

If Target.Value Like "*g*" Then

whenever i try to edit more than one cell at a time.. what gives?

 

[Earl Kiosterud]

Tamale,

If you want to restrict your macro to column A, use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1").EntireColumn) Is Nothing Then Exit Sub
If Target.Value Like "*g*" Then
Target.Font.ColorIndex = 4
ElseIf Target.Value Like "*r*" Then
Target.Font.ColorIndex = 3
ElseIf Target.Value Like "*y*" Then
Target.Font.ColorIndex = 2
End If
End Sub

Or if you have other stuff the Worksheet_Change proc is going to have to do,
then something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If not Intersect(Target, Range("A1").EntireColumn) Is Nothing Then
If Target.Value Like "*g*" Then
Target.Font.ColorIndex = 4
ElseIf Target.Value Like "*r*" Then
Target.Font.ColorIndex = 3
ElseIf Target.Value Like "*y*" Then
Target.Font.ColorIndex = 2
End If
End if
End Sub

Or for the Conditional Formatting solution, if it's just the first character
you want to examine, use Data - Validation - Formula is:
=LEFT(A1) = "r"

This is for where the active (white) cell of your selection is A1. This
will conditionally format the cell when the first character is r or R.

As for the digital signature, you need to get a certificate. Then you can
digitally sign your workbooks, and be just fine with folks with a security
setting of High. Look at Help -- search for "digital certificate" and go
from there.

 

[Tamale]

ack.. fixed the type mismatch myself already.. had to put it in a for
loop of course

now i just want to restrict the range it works on..

 

[Tamale]

ok this is startin to look really good.. here's my current code:

-------------------------------
Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Dim cell As Range

For Each cell In Target
If cell.Column > 2 Then
If cell.Row > 7 Then
If cell.Column < 27 Then
If cell.Row < 54 Then
If cell.Value Like "*g*" Then
cell.Interior.ColorIndex = 10
cell.Font.ColorIndex = 2
ElseIf cell.Value Like "*r*" Then
cell.Interior.ColorIndex = 9
cell.Font.ColorIndex = 2
ElseIf cell.Value Like "*y*" Then
cell.Interior.ColorIndex = 12
cell.Font.ColorIndex = 2
ElseIf cell.Value Like "XXX" Then
cell.Interior.ColorIndex = 48
cell.Font.ColorIndex = 2
Else
cell.Interior.ColorIndex = 0
cell.Font.ColorIndex = 0
End If
End If
End If
End If
End If
Next

End Sub
---------------------------

Now, I realize that the excessive If's are unnecessary, but it does
work well.. so unless you just have a prettier way to do it I think
it's ok..

what I would like now though is for it to be optimized when copying an
entire sheet.. I think what I want is for it to only operate "For Each
<POPULATED> cell In target" or "For Each cell In
<A1-AA200><of>Target"

thanks for all the help so far.....

 

[Earl Kiosterud]

Tamale,

I'm not quite sure I can reconcile

"For Each cell In
<A1-AA200><of>Target"

with your code, but you might mean something like this:
For each cell in Intersect(Target, Range("A1:AA200")

This might execute faster:
Dim Cellrange As Range
Set Cellrange = Intersect(Target, Range("A1:AA200"))
For Each cell In Cellrange

You can replace all those nested if statements with one:
If cell.Column > 2 and cell.Row > 7 and cell.Column < 27 and cell.Row < 54
Then

Or, using good old Intersect again:
If not Intersect(cell, Range("B7:AA54") is nothing then
untested

Or if you prefer to use row and column numbers:
If not Intersect(cell, Range(Cells(7,2), Cells(54,27)) is nothing then
Also untested. But you get the idea.