But that portion of the code would only run if you've changed E14 and E14 is not
"". And the worksheet would be unprotected for a very short period of time.

And since this in in the worksheet_change event (I'd guess), I'd do:

Case "$E$14" 'Approval status
If Target.Value < "" Then
me.unprotect Password:="MyPass"
If lcase(Target.Value) = lcase("Not Submitted") Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
me.protect password:="MyPass"
End If

The Me keyword refers to the object owning the code. In this case, I'm guessing
it's the worksheet getting the change.

And I'd watch out for upper/lower case differences in the string comparison.

Ken Warthen wrote:

Mike,

That doesn't seem very practical since anytime any cell value changes the
code runs.

Ken

"Mike H" wrote:

Ken,

Unprotect at runtime and the re-protect

Case "$E$14" 'Approval status
If Target.Value < "" Then
ActiveSheet.Unprotect Password:="MyPass"
If Target.Value = "Not Submitted" Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
ActiveSheet.Protect Password:="MyPass"
End If

Mike

"Ken Warthen" wrote:

I have the following code in the Worksheet_Change event of a worksheet.

Case "$E$14" 'Approval status
If Target.Value < "" Then
If Target.Value = "Not Submitted" Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
End If

The code works fine, until I turn on the Protect Sheet feature. Then it
generates an error code 1004 (Application-defined or object-defined error).
Does anyone know why protecting the worksheet would cause the code to fail?

Ken

--

Dave Peterson