Clearing cells in a certain row

[bet2466]

Hi im trying to figure out if its possible to clear the contents of a cell by setting a certain value in another cell for example what I want to do is when the value of cell B is set to "in" i want that to clear cells c through h but only for that particular row if that makes sense. Thanks for any help

[Nimrod]

Will this do the trick for you ?
Put this code into the code window for the specific sheet and the deletion should occur.

Private Sub Worksheet_Change(ByVal Target As Range)
 If UCase(Trim(Range("B" & Target.Row).Value)) = "IN" And Target.Column = 2 Then
    Range("$C$" & Target.Row & "1:$H$" & Target.Row).Clear
 End If
End Sub

to copy code in:
-- right click on sheet tab
-- select "view code" (vb window will open)
-- in left "project window" of VB editor double click on desired sheet icon
-- paste the code for above into the Right blank Module area for the specific sheet.
-- close VB editor and test

[bet2466]

Worked like a charm! Thank You Very Much!

[Nimrod]

Cheers...

[bet2466]

one problem though when it clears the cell it also clears out any conditional formatting rules or validations. Is there any way to clear just the text only and not any of the formatting or validations?

[Nimrod]

How about this ?

Private Sub Worksheet_Change(ByVal Target As Range)
 If UCase(Trim(Range("B" & Target.Row).Value)) = "IN" And Target.Column = 2 Then
    Range("$C$" & Target.Row & "1:$H$" & Target.Row).Value = ""
 End If
End Sub

[bet2466]

Perfect!! Thank You!