Cant find a good/right direction to ClearContents

[Craigk19]

I have a WookBook with 2 sheets. It's being used for keeping track of some temp badges for our work place.

Column A has the Temp Badge # and highlighted red or green if there is a name in column c.
Column C is a Name field
Column E is Date and auto genrated when a name is placed in Column C
Column G is in/out using drop down options.

Currently i have it set up so the receptionist only has to Enter in the name field and VBS will auto add the date and Conditional Formatting with change the highlight of column A. I also have VBS set up to when she changes the drop down to In on column G it will take that entire row and copy it all over to sheet2.

What I'm stumped on and cant figure out is how when the drop down is changed to In it will ClearContent on the Name column. I've tried IF formulas with no luck Ideally i would like it If column G was true for the Text "In" then ClearContents of corresponding C column. So changing the In/Out drop down to In would move the data to sheet2 then clear the name column which would in turn changed the color back to green and remove the date. Does anyone have any idea what would be the best way to tackle this?

 

[Craigk19]

bump

 

[NoSparks]

Use the worksheet_change event.
right click the sheet tab and select view code
paste this into the editor and save the workbook as a macro enabled file

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to one cell only
    If Target.Count > 1 Then Exit Sub
    'limit to specific range
    If Not Intersect(Target, Range("G3:G12")) Is Nothing Then
        'check value of target
        If Target.Value = "In" Then
            'copy this row to sheet2
            Target.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            'prevent this sub from calling itself
            Application.EnableEvents = False
            'clear contents in column C
            Target.Offset(, -4).ClearContents
            'remove In entry from G
            Target.Value = ""
            're-enable events
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Craigk19]

Holy crap that worked!! I would by you a beer if possible right now!! been trying to get this to work for half the dang day now.

 

[Craigk19]

Okay I have one last question. In column E I have a data that is auto generated when any text is added to column C. With the above clearing column c it should auto clear column E as well but is not. Where in your vba would I add to clear that cell as well?

 

[Craigk19]

Okay I have one last question. In column E I have a data that is auto generated when any text is added to column C. With the above clearing column c it should auto clear column E as well but is not. Where in your vba would I add to clear that cell as well?

Never mind figured it out.