Dependent cell data validation

[Noreen12123]

Hi everyone (I'm new)!

I have an issue I cannot seem to figure out.

-So I have 2 cells, B1 and B2 say.
-If B2 is 0 I need B1 to stay 0 as well, but I need an error message to pop up if someone changes it.
-If B2 is >0 B1 can be whatever manually entered number you want.

So my steps to doing this were to select B1, go into data validation, Allow: Custom, and I wrote an IF statement as follows
=IF(B2=0,0,B1) ... and set up the error message.

The error message is coming up basically whenever I change B1 now even if B2 is not 0.

I think the if statement may be wrong, but hopefully one of you knows?

 

[mrmmickle1]

Try putting this in the worksheet code module:

[COLOR=#0000ff]Option Explicit[/COLOR]

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change([COLOR=#0000ff]ByVal [/COLOR]Target [COLOR=#0000ff]As[/COLOR] Range)

[COLOR=#0000ff]If[/COLOR] Target.Row = 2 [COLOR=#0000ff]And [/COLOR]Target.Column = 2[COLOR=#0000ff] And [/COLOR]Range("B2") = 0[COLOR=#0000ff] Then[/COLOR]
    Range("B1") = 0
[COLOR=#0000ff]End If[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

If B2 <> 0 Then the value of B1 is not effected. However if B2 = 0 Then the procedure sets B1 = 0

 

[Eric W]

One option is to use Data Validation.

Go to the Data tab. Select cell B1. Click on Data Validation --> Data Validation. On the Settings tab, under Allow: select Custom. In the formula box, enter:
=B2<>0

Then go to the Error Alert tab and put your message in the Error message box.

HTH