Rob_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 198
- Office Version
- 365
- Platform
- Windows
Hello
I have the below code which moves a row of data to another sheet based on a value selected from a list.
When the list item is selected, I'd like the row to be moved to the "LTS" sheet with all conditional formatting removed. Is this possible?
Do I just add
WorkRng.FormatConditions.Delete
underneath BInMove = False?
Kind Regards
Chris
I have the below code which moves a row of data to another sheet based on a value selected from a list.
When the list item is selected, I'd like the row to be moved to the "LTS" sheet with all conditional formatting removed. Is this possible?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fromRow%
Dim archiveRow%
Dim strMatch As String
Dim wsTarget As Worksheet 'sheet to move data to
Dim blnMove As Boolean 'whether to move data or not
Dim blnOnlyValues As Boolean 'determine if it´s the arvjice
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("R2:R1000")) Is Nothing Then 'amend this range address to your
blnOnlyValues = False
Select Case UCase(Target.Value) 'as you have given both "closed" and "Closed"
Case "CLOSED"
Set wsTarget = ThisWorkbook.Worksheets("Archived Absence")
blnMove = True
blnOnlyValues = True
Case "PHASED RETURN"
Set wsTarget = ThisWorkbook.Worksheets("Phased Return")
blnMove = True
blnOnlyValues = True
Case "LTS"
Set wsTarget = ThisWorkbook.Worksheets("Long Term")
blnMove = True
Case Else
blnMove = False
End Select
If blnMove Then
'section of code is taken from your posting
fromRow = ActiveCell.Row
With wsTarget 'only change made here
If .FilterMode Then
strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
archiveRow = Evaluate(strMatch) + 1
Else
archiveRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(3).Row + 1
End If
End With
Range(Cells(fromRow, 1), Cells(fromRow, 19)).Copy wsTarget.Cells(archiveRow, 1)
If blnOnlyValues Then wsTarget.Cells(archiveRow, 1).Resize(1, 19).Value = Cells(fromRow, 1).Resize(1, 19).Value
Rows(fromRow).EntireRow.Delete
Set wsTarget = Nothing 'added line
End If
End If
End Sub
Do I just add
WorkRng.FormatConditions.Delete
underneath BInMove = False?
Kind Regards
Chris
Last edited: