When cell changes value do this...

[kingleviathan]

Hi All</PRE>
I want to set a condition where is a cell changes value, another cell will go blank..</PRE>
I found this code..which work but it does the job too well. It seems </PRE>
to make C6 go blank if you merely click on C5, however, I want C6 to go blank only if you actually make a change to C5</PRE>
Can this be done?</PRE>
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub Else Sheets("Application Form").Range("C6") = "" End If End Sub </PRE>

 

[Sektor]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("C5").Address Then
        Range("C6").Value = vbNullString
    End If
End Sub

 

[kingleviathan]

Hi SeKtor

it still seems to do the job too well. I merely need to click on C5 and the value in C6 disappears.

I want the value in C6 to disppear when I actually make a change in C6

 

[Sektor]

I want C6 to go blank only if you actually make a change to C5

I merely need to click on C5 and the value in C6 disappears

So, what do you want: change to C5 or clicking it?
If you want change, then use Worksheet_Change. If you want click, use Worksheet_SelectionChange.

 

[kingleviathan]

Ok...Say C5 = "test"

If i change it to anything else then I want C6 to be blank.

This works but if I click on C5 and dont make any change then C6 goes blank, I dont want this to happen.

 

[kingleviathan]

Thanks Sektor

I just read the Worksheet_SelectionChange and Worksheet_Change. I just tried it but it crashed as I am already using a Worksheet_SelectionChange

how can i use your code with worksheet_change so it doesnt conflict? do i open a module? procedure? class module?

 

[Sektor]

Oh, yes. I forgot one thing. When you change C5 in code, it triggers again Worksheet_Change. To avoid this, make this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [B]Application.EnableEvents = False[/B]
    If Target.Address = Range("C5").Address Then
        Range("C6").Value = vbNullString
    End If
    [B]Application.EnableEvents = True[/B]
End Sub

 

[Sektor]

In previous post change Worksheet_SelectionChange to Worksheet_Change.