Since there are so many different ways to have VBA work on a specific range, it would help if we knew what the code was doing. It might be as simple as using the With method or a For...Next loop or it might require something more complex than that.

Please click on the following line, read the instructions found via that link and then post your code so we can see what's going on.

1 - It does not appear that you honored my request to follow the instructions found via the link at the end of my post. Had you done that, your code would have looked something like this:

Sub ChgStatus()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Cells.Replace What:="Pending", Replacement:="Done", _
LookAt:=xlPart, MatchCase:=False
Next
End Sub

Since proper indentation makes it easier for us to read and follow the code, please try to post it in that manner in the future. I realize that it doesn't make much difference with the short macro that you posted, but it's a good habit to get into should you ever need to post longer, more complex code.

2 - You said: My code is below, it works for the entire worksheet

In reality, it works for the entire workbook since it loops through every sheet.

For Each WS In Worksheets

I'm not sure if you are asking for it to work on Column E for one sheet or on Column E for all sheets.

In any case, the reason it works on the entire sheet(s) is because you are using the Cells property:

WS.Cells

The Cells property tells VBA to look at every cell in the sheet. If you want to limit it to a specific range, simply replace the Cells property with a Range (or Column) property.

This will work for the entire workbook, but only on Column E of each sheet.

Sub ChgStatus()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Columns("E").Replace What:="Pending", Replacement:="Done", _
LookAt:=xlPart, MatchCase:=False
Next
End Sub

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.