Do things based on previously active cell

[Peteor]

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("W2") <= Range("W1") Then
If Not Intersect(Target, Range("A:U")) Is Nothing Then
Call Automatic_Highlights
Else
End If
End If
End Sub

Which Calls the following:

Sub Automatic_Highlights()
Dim TargetCell As Range
Dim CellReturn As Range


TargetRow = ActiveCell.Row
TargetCol = ActiveCell.Column


ActiveCell.Interior.ColorIndex = 37


Cells(TargetRow, 21).Select


ActiveCell.Interior.ColorIndex = 37


Cells(TargetRow, TargetCol).Select


End Sub

This code functions perfectly except for 1 thing. Imagine you are editing cell A2. When you finish editing cell A2, you click to another cell on the sheet (or even press enter). Sub Automatic_Highlight will then be called for the currently active cell (which will not be cell A2). How do I re-write this for Sub Automatic_Highlight to highlight cell A2 (or whatever cell is being edited, "A2" is just an example)?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("W2") <= Range("W1") Then
   If Not Intersect(Target, Range("A:U")) Is Nothing Then
      Call Automatic_Highlights(Target)
   End If
End If
End Sub
Sub Automatic_Highlights(Rng As Range)

   Rng.Interior.ColorIndex = 37
   Cells(Rng.Row, 21).Interior.ColorIndex = 37

End Sub

 

[Peteor]

Once again, Fluff, You're Awesome! The code functions exactly as intended, and your solution solved my problem.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peteor]

Fluff,

Having this code, I have seen I have another problem related to this question. Imagine cell A2 is edited, and then the highlight occurs. Then the user copys cell A2, selects range A3:A50, and pastes. Currently, it applies all of the highlights to A3:A50, but does not apply all of the highlights to the respective cells in column 21. It only applies it to (Rng.Row, 21) which in this case would be only cell (3,21). What are your thoughts here?

 

[Fluff]

OK, how about

Sub Automatic_Highlights(Rng As Range)

   Rng.Interior.ColorIndex = 37
   Intersect(Rng.EntireRow, Range("U:U")).Interior.ColorIndex = 37

End Sub

 

[Peteor]

That's the one. You always find such eloquent ways to do these things! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peteor]

Fluff,

Sorry to bring the dead back to life here. How would I make this macro not apply to columns O - T, such that it will not affect the highlights?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:T")) Is Nothing Then Exit Sub
If Range("W2") <= Range("W1") Then
   If Not Intersect(Target, Range("A:U")) Is Nothing Then
      Call Automatic_Highlights(Target)
   End If
End If
End Sub