Delete entire row if column has certain text only on specific sheets in workbook

[Plukey]

I have a code that searches Specified sheets Column A for the letter "Y" and copies that row paste to another tab named "CLOSED" I normally go back and manually delete the rows w/ "Y" Im trying to run a code at the end and have a msg box YES/NO to proceed to next code to delete the rows containing Y

Option Explicit
Sub SearchForString()
Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
Dim sSheetsWithData As String, sSheetsWithoutData As String
Dim lSheetRowsCopied As Long, lAllRowsCopied As Long
Dim bFound As Boolean
Dim sOutput As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.CutCopyMode = False
End With

WhatFor = ("Y")
If WhatFor = Empty Then Exit Sub

For Each Sheet In Sheets
If Sheet.Name <> "Sheet5" And Sheet.Name <> "" And Sheet.Name <> "sheet6" And Sheet.Name <> "sheet7" And Sheet.Name <> "sheet8" Then
bFound = False
With Sheet.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole)
If Not Cell Is Nothing Then
bFound = True
lSheetRowsCopied = 0
FirstAddress = Cell.Address
Do
lSheetRowsCopied = lSheetRowsCopied + 1
Cell.EntireRow.Copy Destination:=Sheets("Closed PS").Range("A" & rows.Count).End(xlUp).Offset(1, 0)
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
Else
bFound = False
End If
If bFound Then
sSheetsWithData = sSheetsWithData & " " & Sheet.Name & " (" & lSheetRowsCopied & ")" & vbLf
lAllRowsCopied = lAllRowsCopied + lSheetRowsCopied
Else
sSheetsWithoutData = sSheetsWithoutData & " " & Sheet.Name & vbLf
End If
End With
End If
Next Sheet

If sSheetsWithData <> vbNullString Then
sOutput = "Sheets with data (rows copied)" & vbLf & vbLf & sSheetsWithData & vbLf & _
"Total rows copied = " & lAllRowsCopied & vbLf & vbLf
Else
sOutput = "No sheeTs contained data to be copied" & vbLf & vbLf
End If

If sSheetsWithoutData <> vbNullString Then
sOutput = sOutput & "Sheets with no rows copied:" & vbLf & vbLf & sSheetsWithoutData
Else
sOutput = sOutput & "All sheets had data that was copied."
End If

If sOutput <> vbNullString Then MsgBox sOutput, , "Copy Report"

With Worksheets("Closed PS")
If .Cells(1, 1).Value = vbNullString Then .rows(1).Delete
End With
Application.EnableEvents = True

Set Cell = Nothing
    If MsgBox("Do you want to run this macro?", vbYesNo, "Decisions, Decisions") = vbYes Then

========================================================================somewhere here run this code  on Sheet1,sheet2,sheet3,sheet4
Sub RunCode()
 Dim lRow As Long
    Dim iCntr As Long
    lRow = 390
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 1).Value = "Y" Then
            rows(iCntr).Delete
  

 End If

End Sub

 

[mumps]

So is everything working as you wanted?

 

[Plukey]

Yes, Thank you!

Do you know how to stop this code from changing column B's color back to white?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=True, _
Orientation:=xlTopToBottom
End If
End Sub

 

[mumps]

You are very welcome.
There is nothing in your code that I can see that should affect the color.

 

[Plukey]

thanks again!