Find sheet name and highlight when found..

Hello All,

I have a workbook with two sheets

sheet 1 - master log
sheet 2 - master MTO(material take off)

sheet 2 has a macro that adds sheet and calls it sheet1 then everytime i create a sheet it goes up eg: sheet2,sheet3,sheet4 etc etc etc

in the master log in column E I insert the sheet number and in column H it will turn green and show the word complete.

THIS IS THE MACRO THAT SEARCHES THE SHEETS FOR THE CORRECT NUMBER AND WHEN IT FINDS IT IT HIGHLIGHTS THE CELL IN COLUMN H GREEN AND COMPLETE.
when i remove the sheet# from column E the green and complete goes away.

this code works perfect(thanks to another member ( Fluff13) who was helping me out fix some problems with the original idea. and did an awesome job and nailed every problem i had thank you very much fluff13.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 5 Then Exit Sub
   If IsEmpty(Target) Then
      Target.Offset(, 3).Value = ""
      Target.Offset(, 3).Interior.Color = xlNone
      Exit Sub
   End If
   If Evaluate("isref('" & Target.Value & "'!A1)") Then
      Target.Offset(, 3).Interior.Color = vbGreen
      Target.Offset(, 3).Value = "Complete"
   Else
      Target.Offset(, 3).Value = ""
      Target.Offset(, 3).Interior.Color = xlNone
   End If
End Sub

however this code only works one way. meaning if the sheet is created and i enter the sheet name into the master list it will find it and do exactally what it was meant to do,

problem is .....i need the code to also work the other way, meaning if the sheet name is already on my master list and then i create the new sheet ,, i need it to recogonize that now that sheet is created it can make it green and complete as well.

right now it only does it if the sheet is completed prior to entry into master list


attached is a copy of the workbook, password to open is grappler
can anyone help

thank you so much

fluff13 if your out there thanks for all your help.

Mike Rand

Private Sub ADDSHEET_Click()
Dim i As Long, found As Range, firstfound As String
ActiveSheet.Copy After:=Sheets(Sheets.Count)
On Error Resume Next
Do Until IsNumeric(ActiveSheet.Name)
i = i + 1
ActiveSheet.Name = i
Loop
On Error GoTo 0


Dim GetShape As Shape
For Each GetShape In ActiveSheet.Shapes
GetShape.Visible = False
Next

With Sheets("Master List")
    Set found = .Columns("E").Find(ActiveSheet.Name, , xlValues, xlWhole, 1, 1, 0)
    If Not found Is Nothing Then
        firstfound = found.Address
        Do
            found.Offset(, 3).Interior.Color = vbGreen
            Set found = .Columns("E").FindNext(After:=found)
        Loop Until found.Address = firstfound
    End If
End With

End Sub