Simple if script

illecebrousmonk

New Member
Joined
Aug 15, 2011
Messages
4
Here's what I need, which should be simple, but I'm just learning still.

I need a script which will monitor a cell, A1 (whose value is set by a function on another sheet) and when A1 has a specific value ("home") then it will set the values of three target cells (A2, B2, C2) to three different values ("red", "loud", and "happy").

Here's what I have so far, but when triggered it only sets the first target cell (A2) to its value ("red") and doesn't do anything with the second and third target cells.

Private Sub Worksheet_Calculate()

If Range("$A$1") = "home" Then
Range("$A$2") = "red"
Range("$B$2") = "loud"
Range("$C$2") = "happy"
End If

End Sub

Please help.
Thanks!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Here's what I need, which should be simple, but I'm just learning still.

I need a script which will monitor a cell, A1 (whose value is set by a function on another sheet) and when A1 has a specific value ("home") then it will set the values of three target cells (A2, B2, C2) to three different values ("red", "loud", and "happy").

Here's what I have so far, but when triggered it only sets the first target cell (A2) to its value ("red") and doesn't do anything with the second and third target cells.



Please help.
Thanks!


try doing this



If thisworkbook.sheets.("Sheet1").Range("$A$1").value = "home" Then
thisworkbook.sheets.("Sheet1").Range("$A$2").value = "red"
thisworkbook.sheets.("Sheet1").Range("$B$2").value = "loud"
thisworkbook.sheets.("Sheet1").Range("$C$2").value = "happy"
End If
 
Upvote 0
Can I ask why don't you just use formulas in A2, B2, C2 ?

A2
=IF($A$1="home", "red", "")

B2
=IF($A$1="home", loud", "")

C2
=IF($A$1="home", "happy", "")
 
Upvote 0
A good question. The answer is that the target cells (A2, B2, C2) are normally user entry cells, except in this case (A1 = home). The slightly extended answer is that the data I'm pasting into the target cells is actually much longer than one word.
 
Upvote 0
Bensonsearch,

Didn't see your response initially. Thanks.

Here's a weird thing for me. When I make new excel file your code works fine, but in my actual file it only sets the first cell to "red" then stops...

AP

try doing this
If thisworkbook.sheets.("Sheet1").Range("$A$1").value = "home" Then
thisworkbook.sheets.("Sheet1").Range("$A$2").value = "red"
thisworkbook.sheets.("Sheet1").Range("$B$2").value = "loud"
thisworkbook.sheets.("Sheet1").Range("$C$2").value = "happy"
End If
 
Upvote 0
Does this work?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        <SPAN style="color:#00007F">If</SPAN> .Range("A1") = "home" <SPAN style="color:#00007F">Then</SPAN><br>            .Range("A2:C2") = Array("red", "loud", "happy")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
thats really weird if a new one works but the one you need doesnt.

the other cells arnt locked or called in some other function are they?
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

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
Back
Top