VBA solution for selecting a range of months

[joetejas]

The purpose is to select a month from data validation, and other cells populate future months. Then when selecting a different month those same cells change.

Is there a way to capture multi months in different cells when a case is selected?
Selecting Jan, D2=Feb,D3=Mar,D4=Apr
Selecting Feb D2= Mar ,D3= Apr, D4=May

I have this so far...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C2")) Is Nothing Then
Select Case Range("C2")
Case "Jan": Range("D2").Value = "Feb"
Case "Feb": Range("L2").Value = "Mar"
Case "Mar": Range("M2").Value = "Apr"
Case "Apr": Range("N2").Value = "May"
Case "May": Range("O2").Value = "Jun"
Case "Jun": Range("P2").Value = "Jul"
Case "Jul": Range("Q2").Value = "Aug"
Case "Aug": Range("R2").Value = "Sep"
Case "Sep": Range("S2").Value = "Nov"
Case "Oct": Range("T2").Value = "Dec"
Case "Nov": Range("U2").Value = 10
Case "Dec": Range("V2").Value = 11
End Select
End If


End Sub

 

[Fluff]

You can do it like

      Case "Jan"
         Range("D2").Value = "Feb"
         Range("D3").Value = "Mar"
         Range("D4").Value = "Apr"
      Case "Feb"

 

[joetejas]

Oh yes this works. My next question how can the previous month be deselected when Case "Feb" is active.


I want all future months highlighted and all previous be deselected.




Case "Feb": Range("P1").Value = "Feb"
Range("O1").Value = "Feb"
Range("P1").Value = "Mar"
Range("Q1").Value = "Apr"

 

[Fluff]

What do you mean "deselecting" previous months?

 

[joetejas]

I selected Feb first then Mar, but month Feb is static and was not removed. I only want future months, when selecting a month

[TABLE="width: 1080"]
<tbody>[TR]
[TD="class: xl37730, width: 90"] Mar
[/TD]
[TD="class: xl37730, width: 90"] Apr
[/TD]
[TD="class: xl37730, width: 90"] May
[/TD]
[TD="class: xl37730, width: 90"]Jun[/TD]
[TD="class: xl37730, width: 90"]Jul[/TD]
[TD="class: xl37730, width: 90"]Aug[/TD]
[TD="class: xl37730, width: 90"]Sep[/TD]
[TD="class: xl37730, width: 90"]Oct[/TD]
[TD="class: xl37730, width: 90"]Nov[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 990"]
<colgroup><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"></colgroup><tbody>[TR]
[TD="class: xl37731, width: 90"]Feb
[/TD]
[TD="class: xl37731, width: 90"]Mar
[/TD]
[TD="class: xl37731, width: 90"]Apr[/TD]
[TD="class: xl37731, width: 90"]May[/TD]
[TD="class: xl37731, width: 90"]Jun[/TD]
[TD="class: xl37731, width: 90"]Jul[/TD]
[TD="class: xl37731, width: 90"]Aug[/TD]
[TD="class: xl37731, width: 90"]Sep[/TD]
[TD="class: xl37731, width: 90"]Oct[/TD]
[TD="class: xl37731, width: 90"]Nov[/TD]
[TD="class: xl37731, width: 90"]Dec[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1080"]
<colgroup><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"><col width="90"></colgroup><tbody>[TR]
[TD="class: xl37730, width: 90"][/TD]
[/TR]
</tbody>[/TABLE]

 

[joetejas]

I got, I used
Range("N1").Value = ""

 

[Fluff]

Glad you figured it out & thanks for the feedback

 

[joetejas]

Your welcome,
I do have one more question. Now I have a VBA setup I need a formula to keep value when a month is present or read zero if the month is not present.
[TABLE="width: 252"]
<tbody>[TR]
[TD] -
[/TD]
[TD]Feb
[/TD]
[TD] Mar[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 252"]
<tbody>[TR]
[TD="class: xl37733, width: 84"]$0[/TD]
[TD="class: xl37733, width: 84"]($90,975)[/TD]
[TD="class: xl37733, width: 84"]($104,599)[/TD]
[/TR]
</tbody>[/TABLE]

Current formal in the cell.
=IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4))

 

[Fluff]

Maybe add another if to your formula
=IF(xx="",0,IF($I4="Other - Explain in Comments",
where xx is the cell with the month

 

[joetejas]

Great, I used the following and it zeroed out! I made another row above my columns and if there is no month present the cell will equal 0.

=IF($BD$3<>0,IF($I4="Other - Explain in Comments",
,0)

Maybe add another if to your formula
=IF(xx="",0,IF($I4="Other - Explain in Comments",
where xx is the cell with the month