Clear Cell Based on Content of Another Cell

moheganburner

Board Regular
Joined
Dec 2, 2005
Messages
158
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.



Code:
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Based on what you have, this will look in column F for the word "Ready" and clear the contents of column H
Code:
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

Board Regular
Joined
Dec 2, 2005
Messages
158
Awesome Gibbs, thanks! Just one mistake in your code... that "lastrow" should be "lRow" as previously defined.

MB
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,257
Members
412,313
Latest member
pauloalex
Top