VBA - Getting type mismatch when trying to assign a cell value to a variant variable.

[Entrerri99]

Sorry, this one is driving me crazy. I have the macro below that I'm trying to use to change the color of cells A:M based on the value in column C, however I'm getting a mismatch on this line:
Set CurrentStatus = ActiveSheet.Cells(Rng.Row, StatusColumn).Value
I've done a message box on the ActiveSheet.Cells(Rng.Row, StatusColumn).Value to make sure I was getting the right value and it shows the "Urgent" that is in the cell. Any idea what's up?


Private Sub Worksheet_Change(ByVal Target As Range)
'When the status in Column C is changed, this will make sure the color changes

Dim workRng As Range
Dim Rng As Range
Dim StatusColumn As Integer
Dim CurrentStatus As Variant

StatusColumn = 3
Set workRng = Intersect(Application.ActiveSheet.Range("A:M"), Target)


If Not workRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In workRng
Set CurrentStatus = ActiveSheet.Cells(Rng.Row, StatusColumn).Value
Select Case CurrentStatus
Case "Urgent"
Cells(Rng.Row, workRng).Interior.Color = 8420607
Case "Important"
Cells(Rng.Row, workRng).Interior.Color = 65535
End Select
Next
Application.EnableEvents = True
End If

End Sub

 

[Fluff]

Remove the word Set. You only Set objects.

 

[mumps]

I'm not sure if I understood correctly, but try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim workRng As Range, Rng As Range, StatusColumn As Integer, CurrentStatus As Variant
    StatusColumn = 3
    If Intersect(Target, Range("A:M")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Range("C" & Target.Row).Value
        Case "Urgent"
            Target.Interior.Color = 8420607
        Case "Important"
            Target.Interior.Color = 65535
    End Select
    Application.EnableEvents = True
End Sub

 

[Entrerri99]

Remove the word Set. You only Set objects.

Can't believe it was that simple but thank you so much! I starred at that stupid thing for too long before finding the option to post here.

 

[Fluff]

You're welcome & thanks for the feedback.