Automatically change cell value based on other cell value

etheer · Nov 17th 2012

Re: Automatically change cell value based on other cell value

i mean

now when i change in sheet M2
change cell F in M1 and change cell J to yes but code do not work
if i change normal cell F change cell J to yes and work code
i need work code when i change in sheet M2

Mumps · Nov 17th 2012

Re: Automatically change cell value based on other cell value

Try the attached file.

etheer · Nov 24th 2012

Re: Automatically change cell value based on other cell value

[Blocked Image: http://im29.gulfup.com/Nv7n1.jpg]

Code

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
        On Error Resume Next
For Each c In Range("E7:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        If c = "Yes" Then
            With c
                .Offset(, 1).Value = "Yes"
                .Offset(, 2).Value = .Offset(, -4).Value
            End With
        End If
    Next c
Application.EnableEvents = True
End Sub

Display More

please help me Mumps

i need If in cell G Number no past again new number from cell A to cell G

Mumps · Nov 24th 2012

Re: Automatically change cell value based on other cell value

Try:

Code

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
        On Error Resume Next
For Each c In Range("E7:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        If c = "Yes" Then
            c.Offset(, 1).Value = "Yes"
                If c.Offset(, 2) = "" Then
                    c.Offset(, 2).Value = c.Offset(, -4).Value
                End If
        End If
    Next c
Application.EnableEvents = True
End Sub

Display More

etheer · Nov 24th 2012

Re: Automatically change cell value based on other cell value

Thank You Mumps Code Work 100%

etheer · Nov 26th 2012

Re: Automatically change cell value based on other cell value

Hi Mumps
you can help me
i want code play sound when cell j change to yes
you can edit it please

Code

Private Sub Worksheet_Calculate()
    Dim c As Range
    Application.EnableEvents = False
    On Error Resume Next
    For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row)
        If c = "Yes" Then
            c.Offset(, 1).Value = "Yes"
             If c.Offset(, 2) = "" Then
                c.Offset(, 2).Value = c.Offset(, -6).Value
                c.Offset(, 24).Value = c.Offset(, -8).Value
                c.Offset(, 25).Value = c.Offset(, -7).Value
                c.Offset(, 26).Value = c.Offset(, -6).Value
                c.Offset(, 27).Value = c.Offset(, -6).Formula
                c.Offset(, 28).Value = c.Offset(, 39).Formula
                c.Offset(, 29).Value = c.Offset(, 1).Value
                c.Offset(, 30).Value = c.Offset(, 38).Value
            End If
        End If
    Next c
    Application.EnableEvents = True
End Sub

Display More

file sound

C:\windows\media\ding.wav

Mumps · Nov 26th 2012

Re: Automatically change cell value based on other cell value

Try:

Code

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row)
If c = "Yes" Then
c.Offset(, 1).Value = "Yes"
Application.Speech.Speak("Yes")
If c.Offset(, 2) = "" Then
c.Offset(, 2).Value = c.Offset(, -6).Value
c.Offset(, 24).Value = c.Offset(, -8).Value
c.Offset(, 25).Value = c.Offset(, -7).Value
c.Offset(, 26).Value = c.Offset(, -6).Value
c.Offset(, 27).Value = c.Offset(, -6).Formula
c.Offset(, 28).Value = c.Offset(, 39).Formula
c.Offset(, 29).Value = c.Offset(, 1).Value
c.Offset(, 30).Value = c.Offset(, 38).Value
End If
End If
Next c
Application.EnableEvents = True
End Sub

Display More

etheer · Nov 26th 2012

Re: Automatically change cell value based on other cell value

Thank you code work with sound 100% really you very smart and very professional
You always make me very happy

Mumps · Nov 26th 2012

Re: Automatically change cell value based on other cell value

Thank you. I'm glad the code worked well for you.

etheer · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Hi Mumps :flower:
Why code do not work when protect excel by password :?

Code

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row)
If c = "Yes" Then
c.Offset(, 1).Value = "Yes"
Application.Speech.Speak ("Yes")
If c.Offset(, 2) = "" Then
c.Offset(, 2).Value = c.Offset(, -6).Value
c.Offset(, 24).Value = c.Offset(, -8).Value
c.Offset(, 25).Value = c.Offset(, -7).Value
c.Offset(, 26).Value = c.Offset(, -6).Value
c.Offset(, 27).Value = c.Offset(, -6).Formula
c.Offset(, 28).Value = c.Offset(, 39).Formula
c.Offset(, 29).Value = c.Offset(, 1).Value
c.Offset(, 30).Value = c.Offset(, 38).Value
End If
End If
Next c
Application.EnableEvents = True
End Sub

Display More

Mumps · Dec 1st 2012

Re: Automatically change cell value based on other cell value

If the sheet is protected, the code cannot make any changes to it. You have to add a line at the beginning of the code to unprotect it:

Code

Activesheet. unprotect password:="yourpassword"

and another line at the end of the code to protect it again:

Code

Activesheet. protect password:="yourpassword"

Replace "yourpassword" with the real password that you are using.

etheer · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Code

Activesheet. unprotect password:="1234567" 
 Private Sub Worksheet_Calculate() 
    Dim c As Range 
    Application.EnableEvents = False 
    On Error Resume Next 
    For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row) 
        If c = "Yes" Then 
            c.Offset(, 1).Value = "Yes" 
            Application.Speech.Speak ("Yes") 
            If c.Offset(, 2) = "" Then 
                c.Offset(, 2).Value = c.Offset(, -6).Value 
                c.Offset(, 24).Value = c.Offset(, -8).Value 
                c.Offset(, 25).Value = c.Offset(, -7).Value 
                c.Offset(, 26).Value = c.Offset(, -6).Value 
                c.Offset(, 27).Value = c.Offset(, -6).Formula 
                c.Offset(, 28).Value = c.Offset(, 39).Formula 
                c.Offset(, 29).Value = c.Offset(, 1).Value 
                c.Offset(, 30).Value = c.Offset(, 38).Value 
            End If 
        End If 
    Next c 
    Application.EnableEvents = True 
End Sub
Activesheet. protect password:="1234567"

Display More

Out error message

Mumps · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Try:

[VB]Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect Password:="1234567"
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row)
If c = "Yes" Then
c.Offset(, 1).Value = "Yes"
Application.Speech.Speak ("Yes")
If c.Offset(, 2) = "" Then
c.Offset(, 2).Value = c.Offset(, -6).Value
c.Offset(, 24).Value = c.Offset(, -8).Value
c.Offset(, 25).Value = c.Offset(, -7).Value
c.Offset(, 26).Value = c.Offset(, -6).Value
c.Offset(, 27).Value = c.Offset(, -6).Formula
c.Offset(, 28).Value = c.Offset(, 39).Formula
c.Offset(, 29).Value = c.Offset(, 1).Value
c.Offset(, 30).Value = c.Offset(, 38).Value
End If
End If
Next c
Application.EnableEvents = True
ActiveSheet.Protect Password:="1234567"
End Sub[/VB]

cytop · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Mumps - I admire your patience with this thread, but please edit your post(s), remove the HTML colour tags and replace them with code tags. This is required by the rules here.

You can add code tags by editing your post, highlighting the code and clicking the '#' button on the edit window toolbar. Alternatively, you can add them manually by typing [noparse]

Code

[/noparse][/b] before the start of the code, and [b][noparse]

[/noparse][/b] after. Note that you must remove the HTML tags are they will not be recognised inside the code tags.

Thank you

etheer · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Thank you Mumps
You really professional

Mumps · Dec 1st 2012

Re: Automatically change cell value based on other cell value

Thank you, cytop, for pointing the error out. I have made the corrections.

etheer · Dec 4th 2012

Re: Automatically change cell value based on other cell value

Hi Mumps
Why only code works when the show sheet if hidden or do not show it the code does not work

Code

Private Sub Worksheet_Calculate() 
    ActiveSheet.Unprotect Password:="1234567" 
    Dim c As Range 
    Application.EnableEvents = False 
    On Error Resume Next 
    For Each c In Range("J7:J" & Cells(Rows.Count, "J").End(xlUp).Row) 
        If c = "Yes" Then 
            c.Offset(, 1).Value = "Yes" 
            Application.Speech.Speak ("Yes") 
            If c.Offset(, 2) = "" Then 
                c.Offset(, 2).Value = c.Offset(, -6).Value 
                c.Offset(, 24).Value = c.Offset(, -8).Value 
                c.Offset(, 25).Value = c.Offset(, -7).Value 
                c.Offset(, 26).Value = c.Offset(, -6).Value 
                c.Offset(, 27).Value = c.Offset(, -6).Formula 
                c.Offset(, 28).Value = c.Offset(, 39).Formula 
                c.Offset(, 29).Value = c.Offset(, 1).Value 
                c.Offset(, 30).Value = c.Offset(, 38).Value 
            End If 
        End If 
    Next c 
    Application.EnableEvents = True 
    ActiveSheet.Protect Password:="1234567" 
End Sub

Display More

Automatically change cell value based on other cell value

Participate now!

Share

Users Viewing This Thread