Are you running a version of excel that allows you to protect ranges?

If yes, then you can rely on that to allow you to make the change and use
simpler code like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("C300")

If Target.Cells.Count 1 Then
Exit Sub
End If

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

If LCase(Target.Value) = LCase("qc") Then
Me.Tab.ColorIndex = 3 'red for me
Else
Me.Tab.ColorIndex = xlNone 'change it back
End If

End Sub

But if you're not, your code has to do mo


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range
Dim myPWD As String
Dim UserPWD As String

myPWD = "myPassWorD"

Set myRngToInspect = Me.Range("C300")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

UserPWD = InputBox(Prompt:="What's the password, Kenny?")

If UserPWD < myPWD Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Incorrect password for this cell--reverting to old value!"
Else
If LCase(myRngToInspect.Value) = LCase("qc") Then
Me.Tab.ColorIndex = 3 'red for me
Else
Me.Tab.ColorIndex = xlNone 'change it back
End If
End If
End Sub



Mike wrote:

What i Want is to Type a specific word like QC In cel C300 & have this allow
the Tab to change colour to indicate sheet is complete BY the way Merry
Christmas

"Dave Peterson" wrote:

If you're using xl2002+ (I think this feature was added in xl2002???).

You can protect the sheet, but allow some users to edit certain ranges.

In xl2003 menus:
tools|protection|allow users to edit ranges

Remember that this works only after the sheet is protected.

But if you wanted to use code, I don't think I'd use the workbook_sheetchange
event.

Either the sheet's Worksheet_SelectionChange event or the Worksheet_Change event
would make more sense to me. (But either of these would only work if macros are
enabled and events are enabled.)

Only use one of these--not both.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range
Dim myPWD As String
Dim UserPWD As String

myPWD = "myPassWorD"

Set myRngToInspect = Me.Range("C300")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

UserPWD = InputBox(Prompt:="What's the password, Kenny?")

If UserPWD < myPWD Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Incorrect password for this cell--reverting to old value!"
End If

End Sub

Or use the _selectionchange event...

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRngToInspect As Range
Dim myPWD As String
Dim UserPWD As String

myPWD = "myPassWorD"

Set myRngToInspect = Me.Range("C300")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

UserPWD = InputBox(Prompt:="What's the password, Kenny?")

If UserPWD < myPWD Then
With Application
.EnableEvents = False
.Goto Me.Range("A1") 'send them somewhere else
.EnableEvents = True
End With
MsgBox "Incorrect password for this cell--You can't select it!"
End If

End Sub

These routines don't go into the the ThisWorkbook module. They (just one) go
under the sheet that should have this behavior.


Mike wrote:

I have this macro now i would like to set this cell with a password is this
possible

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("C300")) Is Nothing Then Exit Sub
ActiveSheet.Tab.ColorIndex = 15
End Sub

--

Dave Peterson
.


--

Dave Peterson