Shorter version for this

[wichie]

I want to create a shorter version of the following code:

If Range("f17").Value = "Yes" Or Range("f17").Value = "yes" Then 'For
1st Sunday
Range("f18").Value = ""
Range("p17").Value = ""
Range("p18").Value = ""
Range("J17").Value = ""
Range("J18").Value = ""
ElseIf Range("f18").Value = "Yes" Or Range("f18").Value = "yes" Then
Range("f17").Value = ""
Range("p17").Value = ""
Range("p18").Value = ""
Range("J18").Value = ""
ElseIf Range("p17").Value = "Yes" Or Range("p17").Value = "yes" Then
Range("f17").Value = ""
Range("f18").Value = ""
Range("p18").Value = ""
Range("J17").Value = ""
Range("J18").Value = ""
ElseIf Range("p18").Value = "Yes" Or Range("p18").Value = "yes" Then
Range("f17").Value = ""
Range("f18").Value = ""
Range("p17").Value = ""
Range("J17").Value = ""
Range("J18").Value = ""
ElseIf Range("J17").Value = "Yes" Or Range("J17").Value = "yes" Then
Range("f17").Value = ""
Range("f18").Value = ""
Range("p17").Value = ""
Range("p18").Value = ""
Range("J18").Value = ""
ElseIf Range("J18").Value = "Yes" Or Range("J18").Value = "yes" Then
Range("f17").Value = ""
Range("f18").Value = ""
Range("p17").Value = ""
Range("p18").Value = ""
Range("J17").Value = ""
End If

Thanks in advance

 

[Dave Peterson]

If lcase(Range("f17").Value) = lcase("Yes") Then 'For 1st Sunday
Range("f18,p1718,j17:j18").clearcontents
elseif ....

 

[jan]

wichie,

If you want to toggle the yes-values in the six cells, you could use this
code, placed in the VBA-module belonging to the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rngD As Range
Dim rngTemp As Range
Set rngD = Me.Range("F17:F18,J17:J18,P1718")
If Target.Count = 1 And Not Intersect(Target, rngD) Is Nothing Then
If LCase(Target) = "yes" Then
For Each rng In rngD
If rng.Address <> Target.Address Then
If rngTemp Is Nothing Then
Set rngTemp = rng
Else
Set rngTemp = Union(rngTemp, rng)
End If
End If
Next
rngTemp = Null
End If
End If
End Sub

Otherwise the next code might the shorter one you asked for:

Sub ToggleYes()
Dim rng As Range, rngL As Range, rngD As Range, rngTemp As Range
Set rngD = Me.Range("F17:F18,P1718,J17:J18")
For Each rng In rngD
If LCase(rng) = "yes" Then
For Each rngL In rngD
If rngL.Address <> rng.Address Then
If rngTemp Is Nothing Then
Set rngTemp = rngL
Else
Set rngTemp = Union(rngTemp, rngL)
End If
End If
Next
rngTemp = Null
Exit For
End If
Next
End Sub


Jan