Macro to change a cell value to 0.00 if another cell has a certain name in it.

[zone709]

Trying to change a certain column of row cells to "0.00" 0.00
So lets say H11,H20,H30 have the word "D (Checking)" in it. Then in cells E11,E20,E30 would like to change the cell values to 0.00
I can do an =IF formula in another column and get the results, but im trying to change what i need changed in Column E because i need to leave certain cells alone.
Think i would need to do a small macro so it changes only the cells i indicate above.

Looks like:

Excel 2016 (Windows) 32 bit

	E​	F​	G​	H​
9​	$ 1,352.93	0
10​	$ 869.67	0
11​	$ 1,004.06	0		D (Checking)
12​	$ 548.58	1623
13​	$ 1,114.32	21608

Sheet: Formulas

What I need it to look like:

Excel 2016 (Windows) 32 bit

	E​	F​	G​	H​
9​	$ 1,352.93	0
10​	$ 869.67	0
11​	$0.00​	0		D (Checking)
12​	$ 548.58	1623

Sheet: Formulas

Column E cell changed to 0.00

Any help thanks

 

[Jeffrey Mahoney]

Are the cells in column H formulas, or are the values?

 

[zone709]

yeah your right they are formulas. It pulls the name from another sheet. So D (Checking) is formula, but I can move sheet to another sheet and paste special it if I have to so make this macro work. Doesn't matter.

Basically what I need to do is if any cell in H contains that word to change the value in the same row in Col E to 0.00

<tbody>
</tbody>

 

[Jeffrey Mahoney]

The only way for this to get triggered is if you change a value or formula in column H. Formula results won't cause a trigger.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim Cel As Range
  
  Set i = Intersect(Target, Range("H:H"))
  If Not i Is Nothing Then
    For Each Cel In i
      If Cel.Value = "D (Checking)" Then
        Cells(Cel.Row, 5).Value = 0
      End If
    Next Cel
  End If
    
End Sub

 

[zone709]

ok I understand but I cant get the Private to work.

I usually use

Sub etchdhd ()

End Sub

 

[zone709]

I would like to use this if I can as a

Sub ChangeValue ()

End Sub

If possible thanks

 

[Jeffrey Mahoney]

Sorry, That code needs to go into the WORKSHEET level. Double click on the Sheet name in VBA and paste that code.

 

[zone709]

ok I understand but I need it to be something like this, because I need to add it to a bunch of sub's I'm running to finish up macro. Is it possible I'm sure it is, but not sure how to get around it if I take out target range

Sub etcct ()
  Dim i As Range
  Dim Cel As Range
  
  Set i = Intersect(Target, Range("H:H"))
  If Not i Is Nothing Then
    For Each Cel In i
      If Cel.Value = "D (Checking)" Then
        Cells(Cel.Row, 5).Value = 0
      End If
    Next Cel
  End If
    
End Sub

 

[Jeffrey Mahoney]

You're not going to know which cells actually changed if you run it that way.

 

[Jeffrey Mahoney]

Of course, you can add more code, or even call other subs after you've discovered that cells in column H have been changed.