Change one cell value based on another

Hi

Why do you need a macro for this, As you can do, using formula?

..OO. I understand! So, you are welcome

Try this:

Option Explicit

Sub Button4_Click()
    Dim i As Long
    
    Sheets("New").Activate
    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        If Cells(i, 24).Value = "1" Then
            Cells(i, 18).Value = "Cancelled"
        End If
    Next i

End Sub

I hope that helps.

By the way please check the forum rules.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[code ]
your code here ...
and here ...
and here
[ /code]

... and appear like this when posted:

your code here ...
and here ...
and here

You can also type the code tags in manually if you prefer.

Try like this:

Sub Button5_Click()
    Dim i As Long
    Sheets("New").Activate
    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        If Left(Cells(i, 5).Value, 9) = "Cancelled" And Cells(i, 1).Value = "" Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
End Sub

Or if you want to use Like then:

Sub Button5_Click()
    Dim i As Long
    Sheets("New").Activate
    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        If Cells(i, 5).Value Like "Cancelled*" And Cells(i, 1).Value = "" Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
End Sub

The problem with your code was that you didn't tell which line to delete. And if you dont add the * after cancelled in your like statement, it will only do it for the ones that are exactly cancelled, leaving behind the cancelled<24.

Yeah I know whats wrong here. When you delete the i row, it is possible that the next one, which would be i+1, also meets the criteria but won't get deleted cause it will have turned into the i row, meaning the code won't check it again. So we have to change the current i when the row gets deleted. Also, as the total amount of written rows in your sheet will be changing, we need to update the end of the for accordingly. When this happens, I choose to use the Do Loop strategy instead.

Option Explicit

Sub Button5_Click()
    Dim i As Long 'Is the counter for the loop, will be our line number
    Dim j As Long 'Counts the amount of active lines in sheet
    
    Sheets("New").Activate
    
    Application.Calculation = xlCalculationManual 'Will improve the time it takes to run
    Application.ScreenUpdating = False 'Will stop screen flickering
    
    i = 0
    j = Cells.SpecialCells(xlCellTypeLastCell).Row
    Do
        i = i + 1
        If Not (Cells(i, 18).Value Like "Cancelled*") And Cells(i, 8).Value = "" Then
            Cells(i, 8).EntireRow.Delete
            i = i - 1 'When we delete the row, to avoid not looking at the next one we need to go back
            j = j - 1 'When we delete the row, our sheet has less lines
        End If
        If i = j Then
            Exit Do 'If we reach the last row, the loop ends
        End If
    Loop
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

If you want it not to be like "Cancelled*" then you ccan add exactly that into the code, the Not operator returns True when the statement inside is False. You could be using the <>, but you would have to change it to what I had shown you before:

If Left(Cells(i, 5).Value, 9) <> "Cancelled"

because you need to make sure that what you are checking is exactly "Cancelled". I mean the <> does not work as the Like statement does, in which you can put in the * at the end to say "anything can go after this", but it actually checks for exact matches.

The screen flickering is caused by the fact that we didn't turn off the ScreenUpdating, so you are seeing it delete the rows.

I hope that helps. Please tell me if you have any other issues with this.