VBA to turn a row into values only (based on list item)

[Rob_010101]

Hello,

Column J has a data validation list with the following 3 options:

1. Pending
2. Fit for work
3. Not fit for work

I would like to add VBA which converts the row (from A to I) into values only (i.e converts any formulas into values) when "fit for work" or "not fit for work" is selected in the drop-down list.

I've given it a go myself by trying to copy bits out of other codes but it's all a mish-mash and I'm not skilled enough to get it to work.

Any help appreciated.

Rob

 

[Peter_SSs]

Give this a try (in that worksheet's code module)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("J"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If InStr(1, c.Value, "fit", vbTextCompare) > 0 Then
        With c.EntireRow.Resize(, 9)
          .Value = .Value
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Rob_010101]

Give this a try (in that worksheet's code module)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("J"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If InStr(1, c.Value, "fit", vbTextCompare) > 0 Then
        With c.EntireRow.Resize(, 9)
          .Value = .Value
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

That's resolved it, thanks for your help Peter
Much appreciated

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.