vb help

[Emoncada]

I have this code that works good but need to add something to it.

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim shortTermMemory As Variant
Dim shortTermMemory1 As Variant
Dim shortTermMemory2 As Variant
Dim shortTermMemory3 As Variant
Dim shortTermMemory4 As Variant

If Target.Address = "$B$5" Or Target.Address = "$D$5" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C5"))
shortTermMemory = .Value
Range("b5:d5").Value = vbNullString
.Value = shortTermMemory
End With
Application.EnableEvents = True
End If
End If[/VBA]

Now I have to have 3 Target Address's in the next section. Something like this.
[VBA]
If Target.Address = "$B$7" Or Target.Address = "$D$7" Or Target.Address = "$F$7" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C7"))
shortTermMemory1 = .Value
Range("b7:f7").Value = vbNullString
.Value = shortTermMemory1
End With
Application.EnableEvents = True
End If
End If[/VBA]

I think i need to make a change in the
With Range(Target, Range("C7"))

Can anyone help me with this.

[Emoncada]

I need this to also work with Excel 2007.
Any help would be great.

Here is the entire worksheet vbcode

[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13,F7"), Target) Is Nothing Then
Exit Sub

End If

If Target.Value = "X" Then

Target.Value = ""

Else

Target.Value = "X"

End If

Cancel = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim shortTermMemory As Variant
Dim shortTermMemory1 As Variant
Dim shortTermMemory2 As Variant
Dim shortTermMemory3 As Variant
Dim shortTermMemory4 As Variant

If Target.Address = "$B$5" Or Target.Address = "$D$5" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C5"))
shortTermMemory = .Value
Range("b5:d5").Value = vbNullString
.Value = shortTermMemory
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$7" Or Target.Address = "$D$7" Or Target.Address = "$F$7" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C7", "E7"))
shortTermMemory1 = .Value
Range("b7:f7").Value = vbNullString
.Value = shortTermMemory1
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$9" Or Target.Address = "$D$9" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C9"))
shortTermMemory2 = .Value
Range("b9:d9").Value = vbNullString
.Value = shortTermMemory2
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$11" Or Target.Address = "$D$11" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C11"))
shortTermMemory3 = .Value
Range("b11:d11").Value = vbNullString
.Value = shortTermMemory3
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$13" Or Target.Address = "$D$13" Then
If Target.Value = "X" Then
Application.EnableEvents = False
With Range(Target, Range("C13"))
shortTermMemory4 = .Value
Range("b13:d13").Value = vbNullString
.Value = shortTermMemory4
End With
Application.EnableEvents = True
End If
End If

End Sub
[/vba]

[mdmackillop]

Can you post some data to test this on?

[Emoncada]

File attached

[mdmackillop]

Your project is protected

[Emoncada]

Sorry I forgot

[mdmackillop]

I don't think you need the Change Event
[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13,F7"), Target) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
Select Case .Column
Case 2
.Offset(, 2).ClearContents
.Offset(, 4).ClearContents
Case 4
.Offset(, 2).ClearContents
.Offset(, -2).ClearContents
Case 6
.Offset(, -2).ClearContents
.Offset(, -4).ClearContents
End Select
End If
End With
Cancel = True
End Sub

[/VBA]

[Emoncada]

That looks good MD. Can you help me with something I have been also trying to do with this spreadsheet.

I would like if possible if Value D11 = "X" Then Range("B20") = "N/A" Else if Range("D11").value = "" Then Range("B20") = ""
And I would be able to input a value in b20