VBA Code - If cell value = "xxx" then cell value = "yyy"

I currently have this formula for each cell in a column J

=IF(F3="Content Final","Ready for Processing","") and so on

But i would like this to be run off of VBA code since i do not want users to delete the formula in each cell. Anybody can help me with a code?

this is the current code i am trying out to no avail.

Private Sub Worksheet_Calculate()

    Dim rng As Range, cell As Range

    
    Set rng = Range("f3:f10")

    If Not rng Is Nothing Then

        For Each cell In rng.Cells
            If cell.Value = "Content Final" Then
                cell.Offset(0, 4).AddComment "ready for processing"
            End If
        Next

    End If

End Sub

Hi,

Try using the Worksheet_Change event instead of the Worksheet_Calculate event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
    
    Set rng = Range("f3:f10")

    If Not rng Is Nothing Then

        For Each cell In rng.Cells
            If cell.Value = "Content Final" Then
                cell.Offset(0, 4).AddComment "ready for processing"
            End If
        Next

    End If
End Sub

Hope this helps

thank you very much for you help, but i am getting this message?

2-21-2014 12-43-31 AM.png

Could you please attach your workbook...?

Originally Posted by ajryan88

Could you please attach your workbook...?

Draft-TOC-(Product)-(Applicatiion_Number)-(SCN).xlsm

here is the attached file

so basical;ly if i choose values in column F to content final, then column J should be Ready for processing.....

thanks

oh and password would be 'sub2011' if you need to access them

First of all, I figured you wanted it to be a comment, so in the code that I gave you, change

cell.Offset(0, 4).AddComment "ready for processing"

to

cell.Offset(0, 4).Value = "ready for processing"

Secondly, try unprotecting the VBA and run the macro again, and tell me if you get the same error message.

Delete your Worksheet_Change subroutine and replace it with mine. I believe the error is occurring because you have multiple Worksheet_Change subroutines declared. Also, make the change that I mentioned in my previous post to have the cell value change rather than the cell comment.

Let me know

thank you very much for all your hep. but i am getting this message now? and its freezingout-of-stack.png but the value did change to "ready for processing" its just that it keeps freezing.

i really appreciate all your help, it is 1 am my time and i am hitting the bed. Hope to see your answer tomorrow morining once i awake.

All fixed.

This error was occurring because the procedure was being called again once we changed the value in column J, and Excel doesn't like it when a procedure calls itself.

The solution was to disable events while the value in column J was changed.

Hope this helps

All fixed.

This error was occurring because the procedure was being called again once we changed the value in column J, and Excel doesn't like it when a procedure calls itself.

The solution was to disable events while the value in column J was changed.

Hope this helps

Sorry about the duplicate post, had a problem with the site and it uploaded twice

thank yuo very much! this forum rocks

Ajryan, sorry to bother you again, but i am now trying to offset the column to the left. So basically if values of cells in column J is "Reset to Draft" then cells in column F will equal "Draft"

This is the current code i have but nothing is happening?

Private Sub Worksheet_Change2(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Me.Range("J3:J400")

    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If cell.Value = "Reset to Draft" Then
                Application.EnableEvents = False
                cell.Offset(0, -4).Value = "Draft"
                Application.EnableEvents = True
            End If
        Next
    End If
    
    Set rng = Nothing
End Sub

i figured it out.

Thanks again AJ

You're welcome.

It isn't working because there can only be one Worksheet_Change event, so you need to combine your code into the existing subroutine rather than creating a new one:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Me.Range("F3:F400")

    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If cell.Value = "Content Final" Then
                Application.EnableEvents = False
                cell.Offset(0, 4).Value = "Ready for Processing"
                Application.EnableEvents = True
           End If
        Next
    End If
    
    Set rng = Me.Range("J3:J400")

    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If cell.Value = "Reset to Draft" Then
                Application.EnableEvents = False
                cell.Offset(0, -4).Value = "Draft"
                Application.EnableEvents = True
            End If
        Next
    End If
    
    Set rng = Nothing
End Sub

Give me 5 minutes and I'll rewrite your code so that it is cleaner and easier to read, and I'll post the changed code back here

Here you go, use this subroutine instead of the last one that I posted. Hope this helps

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngF As Range
    Dim rngJ As Range
    Dim cell As Range
    
    Set rngF = Me.Range("F3:F400")
    Set rngJ = Me.Range("J3:J400")
    
    If Not Intersect(rngF, Target) Is Nothing Then
        'Execute this code only if the user changed column F
        If Target.Value = "Content Final" Then
            'This will only change the single affected cell, rather than searching through every cell in rngF
            Application.EnableEvents = False
            Target.Offset(0, 4).Value = "Ready for Processing"
            Application.EnableEvents = True
        End If
    ElseIf Not Intersect(rngJ, Target) Is Nothing Then
        'Execute this code only if the user changed column J
        If Target.Value = "Reset to Draft" Then
            'This will only change the single affected cell, rather than searching through every cell in rngF
            Application.EnableEvents = False
            Target.Offset(0, -4).Value = "Draft"
            Application.EnableEvents = True
        End If
    End If
    
    Set rngF = Nothing
    Set rngJ = Nothing
End Sub

No problems, please don't forget to mark this thread as solved

Thank u!!!