Also, the hyperlink is shrinking the font of the word fail from 9pt to 7pt.
Can we correct the size in the code too?

"Bernie Deitrick" wrote:

That was fairly inefficient code... try it like this (no need for looping). I'm not sure where
your code added "column G" - if you are using another event this code will prevent that from
occuring - but this should handle the color issue, as well as the possibly blank cell in column A.

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).EntireRow. _
SpecialCells(xlCellTypeConstants).Interior.ColorIn dex _
= Target.Interior.ColorIndex


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




HTH,
Bernie
MS Excel MVP


"hshayh0rn" wrote in message
...
I'm looking for someone who can expand on that I already have here.

http://www.microsoft.com/office/comm...286&sloc=en-us

I'm looking to take the above thread a little further. Right now based on a
value in a cell on sheet 1 the entire row is copied to sheet 2. The problem
is the copied row may have a blank value in column A. Column A has values at
the start of each list and then nothing until a new list is started. I need
the code that was detailed in the above link to look in column A upwards
until it finds a value and then use that value.

Also, the copied column is colored. However, when it's pasted into sheet 2
there is an addiitonal column at the end of the row and obviously that column
wouldn't have the same color as the rest of the row. I need the code to
determine the color of the row and make that column's cell the same color.
For example:

row 3 is copied to sheet 2. Column G is the added column on sheet 2 so we
need to color cell G3 the same color as the row we just copied.

Can anyone help me with this?