0

Not a VBA expert at all and would really appreciate any help you experts could offer. I have a spread sheet with 10 columns (A-J) and a "control" column (M). Rows of data will be populated beginning with Row 9, with Row 8 being the header row. Row 7 contains specific formatting to be applied to rows if criteria in column M is met. For several reasons, I need to use VBA rather than conditional formatting. The code I have almost works. It worked on the initial row that met the criteria but none of the subsequent rows. Tried to fix the issue and now none of it works.

The specifics of my current code are:
rngSheet - the area of my spreadsheet with data that needs to be formatted.
rngColorTrigger - Column M containing the criteria. Criteria trigger is if the column value is 0.
rngColor - the cells containing the format to be copied and pasted (Row 7).

My current very-bad-doesn't-work code:

   Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
   Dim rngColor As Range
   Dim rngSheet As Range
   Dim rngColorTrigger As Range
   Set rngColor = Sheet1.Range("rngColor")
   Set rngSheet = Sheet1.Range("rngSheet")
   Set rngColorTrigger = Sheet1.Range("rngColorTrigger")

   ' Limit the copy area to the range for which the trigger is entered
   If Not Intersect(Target, Sheet1.Range("rngColorTrigger")) Is Nothing Then

   ' Only trigger if the value entered is valid (Cell in Column M = 0)
   For Each cell In Range("rngColorTrigger")
       If cell.Value = 0 Then
           Sheet1.Range("rngColor").Select
           Range(Selection, Selection.End(xlDown)).Select
           Selection.Copy
           Sheet1.Range("rngSheet").Select
           Selection.PasteSpecial Paste:=xlPasteFormats

       End If
       Next cell

       ' Reset EnableEvents
       Application.EnableEvents = True
   End If
 End Sub

I have very similar code that copy, cuts, and pastes content between pages based on a valid date being entered in a control column and it works fine...

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
TDMcKinney
  • 1
  • To begin with, you may want to read [this](https://stackoverflow.com/questions/40019981/how-to-avoid-using-select-activate-activesheet-activecell-in-my-specific-vba) it will help make your code a lot simple and robust – cybernetic.nomad Aug 21 '18 at 20:40
  • @cybernetic.nomad - [this thread](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) is a good one too. – BruceWayne Aug 21 '18 at 20:49
  • @BruceWayne - and better than my suggestion -- thanks! – cybernetic.nomad Aug 21 '18 at 20:51
  • @BruceWayne - It is a very good thread. I will see if I can revamp my code. Thank you. – TDMcKinney Aug 21 '18 at 21:00
  • @cybernetic.nomad - thank you, as well. I'm also going to review that thread. – TDMcKinney Aug 21 '18 at 21:00
  • Well, my code certainly looks better and makes more sense when you read it. It still doesn't work. – TDMcKinney Aug 22 '18 at 13:42
  • ' Only trigger if the value entered is valid (Cell in Column M = 0) With Sheet1.Range("rngColorTrigger") .Range("rngColorTrigger") = 0 Sheet1.Range("rngColor").Copy Sheet1.Range("rngSheet").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With – TDMcKinney Aug 22 '18 at 13:47

0 Answers0