macro this maybe fast and easy question but ...

[eric86vabeach]

i want to type something in "B1" and then if it is in sheet 1,2,5 it will get delete and then adds the word "changed" the next cell to the right in "Column B"

Private Sub CommandButton2_Click()

For Each cell In Range(Cells(1, "A"), Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A"))

' i want to replace "B1" with what is typed in "B1" also how do i do this in sheet1 , sheet2 and sheet5 only

If cell.Value Like "B1" Then Rows(cell.Row).ClearContents

Next cell

End Sub

 

[dmt32]

Hi,
have a play with this & see if goes in direction you are looking for. You will need to change sheet name shown in RED where you are entering value in B1.

Private Sub CommandButton2_Click()
    Dim sh As Worksheet
    Dim checkvalue As Variant
    Dim cell As Range
    Dim i As Long

    checkvalue = Sheets("SheetName").Range("B1").Value
    
    If Len(checkvalue) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet5"))
        For Each cell In sh.Range(sh.Cells(1, "A"), sh.Cells(sh.Cells(sh.Rows.Count, "A").End(xlUp).Row, "A"))
            With cell
                If .Value = checkvalue Then
                    .ClearContents
                    .Offset(0, 1).Value = "changed"
                    i = i + 1
                End If
            End With
        Next cell
    Next sh
Application.ScreenUpdating = True
MsgBox i & " Records Updated.", 48, "Records Updated"
End Sub

Dave

 

[eric86vabeach]

thanks it works