Change the value of a cell to 0 based on the value of another cell

Hello,

How can I Change the value of celsl to 0 based on the value of another cell.

For example, if the value in cell A1 = "DN" then the value of B1:B6 = 0

Thanks,

Hi
Use this formula

Please Login or Register  to view this content.

Regards

Or

=(A1="DN")*B1

Thanks for your answers. But this formula will not do because I need to enter numbers in the cells in which you suggested to use this formula (B1:B6). If A1 = DN, I want the numbers in B1:B6 to change to 0. I think I need a macro to do this.

Regards,,

I'm using this code below. But there is something worng. Can anyone correct this code?



Private Sub worksheet_change(ByVal target As Range)
If [b3] = "DN" Then
[C3:C5] = 0
End If
End Sub


Regards,

I would try:

Please Login or Register  to view this content.

Thanks a lot.

If this solved your problem, please don't forget to mark your thread as solved. (Edit your intial message, then click on "Go Advanced" and change the prefix for the message subject.)

I'm trying to apply this code to different ranges. I'm using the code below, but it gives an error.

Private Sub worksheet_change(ByVal target As Range)
If Range("b3").Value = "DN" Then
Range("C3:C5").Value = 0
If Range("b10").Value = "DN" Then
Range("C10:C15").Value = 0
End If
End If
End Sub



Can anyone help correct this code?

Thank you,

What error is it giving you? Also the way it's written, if B3 is "DN", it will change C3:C5 and then it will check if B10 is "DN". If B3 isn't "DN", then it won't check B10 at all. If this isn't desired, I'd move one of the "End If"s to before the "If Range("b10")..." line.

I tried to move of the End Ifs to before the "If Range("b10")..." line.
When I enter DN in B3, C3:C5 is changed. But when I enter DN in B10, C10:C15 is changed but the excel workbook freezes and doesn't respond.

I want what I mentioned but without the workbook freeze.

Thanks,

bkutkut

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I tried the code below. When I enter "DN" in B3, C3:C5 is changed to 0. When I keep "DN" in B3 and enter "DN" in B10, C10:C15 is changed to 0 AND the worksheet stops responding. It seems that there is a mistake in this code. Can anyone help correct this code?
Here's the code:

Please Login or Register  to view this content.

Regards,

I would start a new thread, with the relevant information regarding the code you're using. Pay attention to the Forum Rules (Rule #4 specifically).