Clear Cell Based on Content of Another Cell

[moheganburner]

Hi guys. On "Sheet2", if a cell in Column F has the word "Ready" in it, I want to clear the contents of the corresponding cell in Column H. I'm picturing an if statement. I know this code doesn't do anything, but I didn't want to come to the board empty handed.

Sub ClearCell()
Dim Sheet2 As Long
Set Ws5 = Worksheets("Sheet2")
  
    With Ws5
        For x = 2 To Sheet2 'because I want to start searching in row 2

        Set foundRng = searchRng.Find(Ws5.Cells(x, 8))
            If foundRng Is Nothing Then
            Ws5.Range("H65536").ClearContents
            End If
        Next x
    End With
End Sub

 

[XLGibbs]

Based on what you have, this will look in column F for the word "Ready" and clear the contents of column H

Sub ClearCell()
Dim Ws5 As Worksheet
Dim lRow as Long
Set Ws5 = Worksheets("Sheet2")
 
lRow = Ws5.Range("H65536").End(xlup).Row 'sets last row to last row of data with something in column H

    With Ws5
        For x = 2 To lastrow 'because I want to start searching in row 2
        If Cells(x,6) = "Ready" then cells(x,8).ClearContents
        Next x        
    End With
End Sub

 

[moheganburner]

Awesome Gibbs, thanks! Just one mistake in your code... that "lastrow" should be "lRow" as previously defined.

MB

 

[XLGibbs]

Your welcome. Happy holidays. There are more potentially "slick" ways rather than going row by row....depending on if that meeted your exact needs or not.

Post back if problems,