Changing a cell automatically when another cell changes

PuckMark

New Member
Joined
Oct 24, 2011
Messages
31
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

PuckMark

New Member
Joined
Oct 24, 2011
Messages
31
Ok, a little bit more info here, and another request for help. My code now looks like this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top