Excel VBA - Add hyphens to column A if column B contains specific letter-CodePudding

Please help a newbie. Using Excel VBA, I am trying to format the text in column A with hyphens but only if column B contains the letter B.

I have found the code below, one which formats the cells in column A with hyphens, and another code which checks column B for the correct value, but cannot seem to combine them to work. Help please. Thank you.

Sub AddDashes()
  Dim Cell As Range
  On Error GoTo NoFilledCells
  For Each Cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    Cell.Value = Format(Replace(Cell.Value, "-", ""), "@@@@@-@@@-@@@@")
  Next
NoFilledCells:
End Sub

and

Sub ChangeColumn()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("B" & i).Value = "B" Then
            Range("A" & i).Value = "Formatted text with hyphens as above"
        End If
    Next i
End Sub

CodePudding user response：

Option Explicit

Sub AddDashes()

    Dim ws As Worksheet, cell As Range
    Dim LastRow As Long
    Set ws = ActiveSheet

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In ws.Range("A2:A" & LastRow)
        If cell.Offset(0, 1) = "B" Then ' col B
            cell.Value = Format(Replace(cell.Value, "-", ""), "@@@@@-@@@-@@@@")
        End If
    Next

End Sub