VBA Clear Conditional Format

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. 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?

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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Have a try, I added these three lines to your macro. I also added Application.EnableEvents to avoid redundant triggering of the event Worksheet_Changewhen deleting rows.
VBA Code:
'...
Range(Cells(fromRow, 1), Cells(fromRow, 19)).Copy wsTarget.Cells(archiveRow, 1)
With wsTarget                                                                     '<-- added
    .Range(.Cells(archiveRow, 1), .Cells(archiveRow, 19)).FormatConditions.Delete '<-- added
End With                                                                          '<-- added
If blnOnlyValues Then wsTarget.Cells(archiveRow, 1).Resize(1, 19).Value = Cells(fromRow, 1).Resize(1, 19).Value
Application.EnableEvents = False      '<-- added
Rows(fromRow).EntireRow.Delete
Application.EnableEvents = True       '<-- added
Set wsTarget = Nothing
'...
 
Upvote 0
Solution
PS. are you sure you're deleting the right row ? shouldn't it be fromRow = Target.Row ? or maybe not.
 
Last edited:
Upvote 0
PS. are you sure you're deleting the right row ? shouldn't it be fromRow = Target.Row ? or maybe not.
It seems to work correctly as it is... I've been using it a while now and not noticed any weird behaviour.

Thanks very much for your help on this.
 
Upvote 0
Glad I've been of some help(y).
No idea on what your project goal is but if you are moving a row to a different destination then I would delete the 'target' row (source). With fromRow = ActiveCell.Row you will select the row below the target.
Got it !!! It's ok because you are using Validation dropdown that doesn't change cell when item is choosen. I, instead, was testing with manual change to cells in column R and that would change cell focus.
 
Upvote 0
Glad I've been of some help(y).
No idea on what your project goal is but if you are moving a row to a different destination then I would delete the 'target' row (source). With fromRow = ActiveCell.Row you will select the row below the target.
Got it !!! It's ok because you are using Validation dropdown that doesn't change cell when item is choosen. I, instead, was testing with manual change to cells in column R and that would change cell focus.
Glad I've been of some help(y).
No idea on what your project goal is but if you are moving a row to a different destination then I would delete the 'target' row (source). With fromRow = ActiveCell.Row you will select the row below the target.
Got it !!! It's ok because you are using Validation dropdown that doesn't change cell when item is choosen. I, instead, was testing with manual change to cells in column R and that would change cell focus.
That’s probably it. Just another question if I may, where (or how) did you learn VBA? All the stuff I’m using at the moment is from this forum but I’d really like to learn myself.
 
Upvote 0
At first (still now) I read many books (tomes of 300-400 pages) that explained all the functions integrated in Excel (in truth I started with Lotus 123 and that was many years ago).
For me it was like reading the index of the technical manual of a mechanical machine and taking notes of ready-meals (code snippets from forums now that the web exists). When the machine breaks, I check the contents of the index and go directly to the solution.
With Excel I do the same, decide my goal (analysis), divide it into small parts (flow-chart) and start thinking about which would be the best function to use in each part, then go to the index to see it's syntax and then a lot of debuging to fix issues. Over time you build your own experience that will allow you many shortcuts.
 
Upvote 0
At first (still now) I read many books (tomes of 300-400 pages) that explained all the functions integrated in Excel (in truth I started with Lotus 123 and that was many years ago).
For me it was like reading the index of the technical manual of a mechanical machine and taking notes of ready-meals (code snippets from forums now that the web exists). When the machine breaks, I check the contents of the index and go directly to the solution.
With Excel I do the same, decide my goal (analysis), divide it into small parts (flow-chart) and start thinking about which would be the best function to use in each part, then go to the index to see it's syntax. Over time you build your own experience that will allow you many shortcuts.
Thanks for the helpful insight :). Guess I’ll have to start reading! Thought I was done with all of that after my masters but guess it’s the only way.

Thanks again for your help.
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top