CODEWITHSUNDEEP
vba
Tim Harris
Tim Harris

Reputation: 13

VBA/Excel: How to highlight a cell based on changes for only 1 column

I have a small macro that highlights cells when they are edited based on what month it is. I would like to make this subroutine happen for only Column D. Is there a way to do this? Code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Month(Date)
Case Is = 1
Range(curCell).Interior.ColorIndex = 20
Case Is = 2
Range(curCell).Interior.ColorIndex = 24
Case Is = 3
Range(curCell).Interior.ColorIndex = 33
Case Is = 4
Range(curCell).Interior.ColorIndex = 18
Case Is = 5
Range(curCell).Interior.ColorIndex = 23
Case Is = 6
Range(curCell).Interior.ColorIndex = 45
Case Is = 7
Range(curCell).Interior.ColorIndex = 22
Case Is = 8
Range(curCell).Interior.ColorIndex = 38
Case Is = 9
Range(curCell).Interior.ColorIndex = 35
Case Is = 10
Range(curCell).Interior.ColorIndex = 31
Case Is = 11
Range(curCell).Interior.ColorIndex = 44
Case Is = 12
Range(curCell).Interior.ColorIndex = 48
End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
curCell = ActiveCell.Address(Columns(0, 0))
End Sub

Upvotes: 1

Views: 379

Answers (1)

KekuSemau
KekuSemau

Reputation: 6852

You can try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Application.Intersect(Target, Me.Columns("D"))
    If Not r Is Nothing Then
        ' proceed with r here instead of Target
        ' ...
    End If
End Sub

Actually, it seems you didn't use Target right yet. It gives you the Range Object where the change has taken place. You don't need that curCell.

Upvotes: 1

Related Questions