This Should be Easy::Event Handler Maybe?

[Saladsamurai]

I want to make it so that if a "$" symbol or the letter "c" is entered into a cell on WorkSheets(1) then the same symbol is automatically entered onto WorkSheets(2) in the same cell location.

[Saladsamurai]

This works.... but I was hoping I wouldn't have to update the values of i and j everytime they change. And Maybe avoid looping altogether?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For i = 1 To 100
For j = 1 To 100
If Worksheets(1).Cells(i, j) = "c" Then
Worksheets(2).Cells(i, j) = Worksheets(1).Cells(i, j)
End If
If Worksheets(1).Cells(i, j) = "$" Then
Worksheets(2).Cells(i, j) = "$"
End If

Next j
Next i

End Sub

[Saladsamurai]

This works.... but I was hoping I wouldn't have to update the values of i and j everytime they change. And Maybe avoid looping altogether?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For i = 1 To 100
For j = 1 To 100
If Worksheets(1).Cells(i, j) = "c" Then
Worksheets(2).Cells(i, j) = Worksheets(1).Cells(i, j)
End If
If Worksheets(1).Cells(i, j) = "$" Then
Worksheets(2).Cells(i, j) = "$"
End If

Next j
Next i

End Sub




Okay! Here is the problem with this: If I enter a "$" or "c" on 1 it gets placed in 2...that just Fine.

BUT, if I delete a "$" or "c" from 1 it STAYS on 2. Not good.

[GTO]

Not anywhere near sure, but maybe:


Option Explicit
Dim bolWasChar As Boolean

Private Sub Worksheet_Activate()
If Selection.Count = 1 Then
If Selection.Value Like "[Cc[$]" Then
bolWasChar = True
Else
bolWasChar = False
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Value Like "[Cc[$]" Then
Sheet2.Range(Target.Address).Value = Target.Value
ElseIf bolWasChar And Not Target.Value Like "[Cc[$]" Then
Sheet2.Range(Target.Address).ClearContents
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Value Like "[Cc[$]" Then
bolWasChar = True
Else
bolWasChar = False
End If
End If
End Sub


Hope that helps,

Mark