Hello all,
While perusing the interwebs, I found the following sub below. The idea is that when a cell is changed in the worksheet that it runs a case statement against what the changed cell's new value is. It seems pretty straight forward but when I run it in debug mode the Set Rng1 = Range(Target.Address) is making Rng1 = the target's VALUE and not the CELL. So if I change V16 to J then Rng1 = J and the Case statement then apparently cycles through every cell in column J until it errors out. What I don't understand is that when I hover over Range(Target.Address) it shows Target.Address is $V$16 which is correct... So why is it setting Rng1 = "J" (the value)??
If Rng1
should equal "J" (what I change the cell to) and not "V16" (the cell that was changed), what should I use so that the Switch statement only cycles through the 1 changed cell instead of a whole column?
Please advise.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Range(Target.Address)
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "J", "B", "S"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Target.Value = UCase(Target.Value)
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End Sub
Case Statement on Changed Cell's Value
