Changing Cell Value Based on Another Cell Text - NOOB :(

[yazanibrahim1984]

Hello,

I'm super noob/beginner with macros and I'm writing a code to change the value for cells in column E to 0.00 if cells in column K has "Yes". Here's what I have:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$" And Target.Value = "Yes" Then
Range("$E$").Value = "0.00"
End If
End Sub


But this doesn't work because:

1. it's only working on one cell
2. if I remove the word "Yes" from the cell in column K, the value remains 0.00 in column E and I cannot UNDO/Ctrl+Z to go back to the original value.

Any help/suggestion is appreciated.

 

[Twollaston]

Hello,

I'm super noob/beginner with macros and I'm writing a code to change the value for cells in column E to 0.00 if cells in column K has "Yes". Here's what I have:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$" And Target.Value = "Yes" Then
Range("$E$").Value = "0.00"
End If
End Sub

("$E$")
and
"$K$"

don't put them like that
for E you need to put the row number for example
Range("E1") if row 1 or Range("E1") if row 2
And do the same thing for K put the row number

 

[kweaver]

Try this:

Sub ChangeColE()
Dim LR As Long
Dim i As Integer
LR = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To LR
If Cells(i, 11) = "Yes" Then
  Cells(i, 5) = 0
  Cells(i, 5).NumberFormat = "0.00"
Else
End If
Next
End Sub

 

[yazanibrahim1984]

Suggestion 1 didn't work (Can you write the full code)

Suggestion 2, Excel kept crashing & will not auto run

 

[Twollaston]

Suggestion 1 didn't work (Can you write the full code)

Suggestion 2, Excel kept crashing & will not auto run

The code posted by kweaver works fine for me, just go to excel hit F11 and past it into a normal module, and then when you run it it will update the cells for you
How many rows of data do you have?
Are you posting it in the Worksheet page or in a module?

	A	B	C	D	E	F	G	H	I	J	K
1	x	x	x	x	0.00	x	x	x	x	x	Yes
2	5	5	5	5	0.00	5	5	5	5	5	Yes
3	5	5	5	5	5	5	5	5	5	5	No
4	5	5	5	5	0.00	5	5	5	5	5	Yes
5	5	5	5	5	0.00	5	5	5	5	5	Yes
6	5	5	5	5	0.00	5	5	5	5	5	Yes
7	5	5	5	5	0.00	5	5	5	5	5	Yes
8	5	5	5	5	0.00	5	5	5	5	5	Yes
9	5	5	5	5	0.00	5	5	5	5	5	Yes
10	#	#	#	#	0.00	#	#	#	#	#	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I'll see if I can find a worksheet even for something like this

 

[Twollaston]

Suggestion 1 didn't work (Can you write the full code)

Suggestion 2, Excel kept crashing & will not auto run

You can try this. I'm not well versed in worksheet events, so if anyone sees that i'm doing something stupid in this let me know.

This one is working for me. Just right click the sheet you want to use this on and click view code. Make sure it's pasted into the worksheet and not a module or else it won't work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Range("K1").End(xlDown)) Is Nothing Then Exit Sub


Dim LR As Long
Dim i As Integer
LR = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To LR
If Cells(i, 11) = "Yes" Then
  Cells(i, 5) = 0
  Cells(i, 5).NumberFormat = "0.00"
Else
End If
Next
End Sub

 

[yazanibrahim1984]

This code worked for a day & I saved my Excel sheet as Macro-Enabled, but today it wasn't working

 

[yazanibrahim1984]

Quick update... The code is actually working, but it's case sensitive.


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("K1").End(xlDown)) Is Nothing Then Exit Sub

Dim LR As Long
Dim i As Integer
LR = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To LR
If Cells(i, 11) = "Yes" Then
Cells(i, 5) = 0
Cells(i, 5).NumberFormat = "0.00"
Else
End If
Next
End Sub


However, I noticed that I cannot Go Back (Ctrl Z) once I type "Yes", is there a way to correct it in the macro command?