Changing case and also changing entered data
 

I have a sheet that in column D I will be entering a value of "p" or "f". I
would like code behind the scenes checking column D for a "p" or "f" as well
as "P" or "F" and changing that "p" or "f" to "Pass" or "Fail". Now I also
have some on change code already looking at that cell so I need this code to
append to that code. Here is the code I already have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim myR As Long

If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 4 Then Exit Sub
If UCase(Target.Value) < "FAIL" Then Exit Sub

Application.EnableEvents = False
myR = Sheets("Notes").Cells(Rows.Count, 4).End(xlUp).Row
Target.EntireRow.Copy Sheets("Notes").Cells(myR + 1, 1).EntireRow
If Target.EntireRow.Cells(1, 1).Value = "" Then
Worksheets("Notes").Range("A" & myR + 1).Value = _
Target.EntireRow.Cells(1, 1).End(xlUp).Value
Else
Worksheets("Notes").Range("A" & myR + 1).Value = _
Target.EntireRow.Cells(1, 1).Value
End If
Sheets("Notes").Cells(myR + 1, 1).Resize(1, 7).Interior.ColorIndex _
= Target.Interior.ColorIndex
Sheets("Notes").Cells(myR + 1, 1).Resize(1, 8).Interior.ColorIndex _
= Target.Interior.ColorIndex

Sheets("Notes").Cells(myR + 1, 7).BorderAround xlContinuous, xlThin
Sheets("Notes").Cells(myR + 1, 8).BorderAround xlContinuous, xlThin

Adden = "Notes!H" & myR
ActiveSheet.Hyperlinks.Add Anchor:=Target, _
Address:="", SubAddress:=Adden, _
TextToDisplay:="Fail"
Application.EnableEvents = True
End Sub

Changing case and also changing entered data
 

You probably want this just after your Dim statements since you have one
that exits the sub if the target equals "FAIL".

If LCase(Target.Value) = "p" Then
Target.Value = "Pass"
ElseIf LCase(Target.Value) = "f" Then
Target.Value = "Fail"
End If


"hshayh0rn" wrote in message
...
I have a sheet that in column D I will be entering a value of "p" or "f". I
would like code behind the scenes checking column D for a "p" or "f" as
well
as "P" or "F" and changing that "p" or "f" to "Pass" or "Fail". Now I also
have some on change code already looking at that cell so I need this code
to
append to that code. Here is the code I already have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim myR As Long

If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 4 Then Exit Sub
If UCase(Target.Value) < "FAIL" Then Exit Sub

Application.EnableEvents = False
myR = Sheets("Notes").Cells(Rows.Count, 4).End(xlUp).Row
Target.EntireRow.Copy Sheets("Notes").Cells(myR + 1, 1).EntireRow
If Target.EntireRow.Cells(1, 1).Value = "" Then
Worksheets("Notes").Range("A" & myR + 1).Value = _
Target.EntireRow.Cells(1, 1).End(xlUp).Value
Else
Worksheets("Notes").Range("A" & myR + 1).Value = _
Target.EntireRow.Cells(1, 1).Value
End If
Sheets("Notes").Cells(myR + 1, 1).Resize(1, 7).Interior.ColorIndex _
= Target.Interior.ColorIndex
Sheets("Notes").Cells(myR + 1, 1).Resize(1, 8).Interior.ColorIndex _
= Target.Interior.ColorIndex

Sheets("Notes").Cells(myR + 1, 7).BorderAround xlContinuous, xlThin
Sheets("Notes").Cells(myR + 1, 8).BorderAround xlContinuous, xlThin

Adden = "Notes!H" & myR
ActiveSheet.Hyperlinks.Add Anchor:=Target, _
Address:="", SubAddress:=Adden, _
TextToDisplay:="Fail"
Application.EnableEvents = True
End Sub