title: VBA to Clear a content of a cell if another cell is changed or cleared while KEEPING conditional formatting rules
body: <p>I have googled this issue, but I can't seem to find the right answer anywhere. I'm new to VBA so excuse me if my question sounds dumb.</p>
  
  <p>I have a VBA code that is found in the worksheet section. </p>
  
  <p><em>When Cell E13 is changed or cleared, cell Q13 is cleared. Similarly, if E14 is changed or cleared, cell Q14 is cleared. This goes all the way down to row number 499.</em> This also applied to E3 and Q3.
  Please note that Q3 has conditional formatting rules that are different than the conditional formatting rules of Q13-Q499. Also, Q13-Q499 share the same conditional formatting rules.</p>
  
  <p>However, <strong>in rows 13-499</strong>, when a cell in column E, say E20, is cleared, this changes the conditional formatting rule of its relative cell in column Q, which is Q20. For some reason, clearing E20 seems to apply to Q20 the conditional formatting rule of Q3.</p>
  
  <p>Could anyone help me adjust the code so it does what it currently does, but also keeps my conditional formatting rules?</p>
  
  <p>This might help. Here are the conditional formatting rules that I have in column Q.</p>
  
  <p><strong>Q3:</strong>
  Q3 default fill color is "Blue" and default borders is "All Borders".
  1) IF E3="Company", change the fill color of Q3  to "White" and keep borders as "All Borders".
  2) If Q3&lt;>"Company", keep fill color as "Blue" and change the borders to "Left Border" only.</p>
  
  <p><strong>Q13-Q499</strong>
  Q13-Q499's default fill color is "White" and default borders is "All Borders".
  1) If Q13-Q499="Company", keep fill color as "White" and keep borders as "All borders".
  1) IF Q13-Q499&lt;>"Company", change the fill color of Q13-Q499  to "Grey" and change borders to both "Left &amp; Right Borders".</p>
  
  <p>The VBA code that I am using is as the following. It is pasted in the worksheet section:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      With Target
          If .Column = 5 Then
              Rem Clear Columns Q
              .Cells.Offset(, 12).ClearContents
  
              Rem Enable Events after changes
              Application.EnableEvents = 1
  
            End If: End With
            End If
            End Sub
  </code></pre>
  
  <p>I think I just figured out the problem. I have a Sub Copyrow() code pasted in the module. It is used to copy contents of a group of cells into the last non-blank row of my spreadsheet when a button is clicked.Since cell Q3 has a conditional formatting rule that changes its color, it seems to be copying the conditional formatting of Q3 too. Could you please fix my code, so that only when it comes to copying Q3, that the code only 'paste speacial' VALUES instead of everything. Here is my Module code:</p>
  
  <pre><code>Option Explicit
  
  Sub copyRow()
  Dim ws As Worksheet
  Dim lRow As Long
  
  ' define which worksheet to work on, i.e. replace Sheet1 with the name of 
  your sheet
  Set ws = ActiveWorkbook.Sheets("1. Clients Details")
  
  ' determine the last row with content in column D and add one
  lRow = ws.Cells(Rows.count, "D").End(xlUp).Row + 1
  
  ' copy some cells into their ranges
  ws.Range("D3:F3").Copy ws.Range("D" &amp; lRow)
  ws.[D1].Select
  
  ' combine G3, H3, I3, J3 and copy into column E, next empty row
  ws.Range("G" &amp; lRow) = ws.[G3] &amp; " " &amp; ws.[H3] &amp; ", " &amp; ws.[I3] &amp; " " &amp; ws.
  [J3] &amp; " "
  
  ' copy the other cells into their ranges
  ws.Range("K3:P3").Copy ws.Range("K" &amp; lRow)
  ws.[K1].Select
  
  ' combine G3 &amp; H3
  ws.Range("Z" &amp; lRow) = ws.[G3] &amp; " " &amp; ws.[H3]
  
  ' combine I3, J3
  ws.Range("AA" &amp; lRow) = ws.[I3] &amp; "       " &amp; ws.[J3]
  
  
  ' copy Q3 into column Q only, if Q3 = "Company"
  If Worksheets("1. Clients Details").Range("E3").Value = "Company" Then
  ws.Range("Q3").Copy ws.Range("Q" &amp; lRow)
  
  
  End If
  ActiveSheet.Cells(Rows.count, "D").End(xlUp).Activate
  End Sub
  </code></pre>