Changing a cell automatically when another cell changes

[PuckMark]

This may be a very easy question, I'm not sure. I'm very limited in my usage of VB, but I've been trying to find a solution to this for a few hours and can't do it. Hopefully one of you can help me quickly.

In my spreadsheet, cell C7 contains a drop down box with 2 possible choices (SS or CBG). If the first choice (SS) is selected, I want cell C9 to return a value of 1 and be conditionally formatted to disappear to the end user. If the second choice (CBG) is selected, then cell C9 should appear with a drop down box offering the values of 1-10 as options. The conditional formatting is the easy part, and is already done. The part I do not know how to do is making C9 = 1 when C7 is changed. I don't want C9 to be a formula, since the user will need to have options to change it if C7 = CBG.

I hope I have explained this well enough....please help me if you can.

Regards,
Mark

 

[PuckMark]

Ok, a little bit more info here, and another request for help. My code now looks like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("c9"), Target) Is Nothing Then
        Rows("15:24").EntireRow.Hidden = True
        Rows("15:" & Range("c9").Value + 14).EntireRow.Hidden = False
 
    End If
 
End Sub
Private Sub Worksheet_Calculate()
If Range("c7").Value = "Single SKU" Then
    Application.EnableEvents = False
    Call Changeto1
    Application.EnableEvents = True
End If
End Sub

Cell C7 is a drop down box with only 2 options - Single SKU, or CBG. If Single SKU is selected, cell C9 should equal the value of 1, and lines 16-24 should be hidden. If CBG is selected in cell C7, then C9 should appear with a drop down box of the values 1-10, and the lines will hide depending on the selection in C9. That works brilliantly, thanks to the code above and thanks to the help of the good people on this board.

HOWEVER, my lone remaining issue is this: when I change cell C7 to "Single SKU", cell c9 changes to the value of 1, lines 16:24 should be hidden, and they do not hide. If I go into cell C9 and edit the cell and hit enter, then the lines hide. Is there a way to get the lines to hide without having to do this?

I don't know if I'm making sense - I've been at this for 48 hours and I'm starting to see VBA code in my sleep. Can anybody help me out?

Regards,
Mark