Have to reselect cell to get macro to finish tasks.

[SS Rookie]

When a cell with a drop down list changes values, my macro performs actions depending on the value the drop down cell is changed too. The macro works except to get it to finish or change the color of a cell you have to click off the drop down cell and then reselect it. Is there something I need to add to the code to get the color to change at the same time it is doing the strikethrough? Copy of the If statement below.

If rngCurrent.Value = rngValue.Value Then
Range("B" & rngCurrent.Row & ":Q" & rngCurrent.Row).Font.Strikethrough = True
Range("M" & rngCurrent.Row).Interior.Color = Range("L" & rngCurrent.Row).Interior.Color
Else
Range("B" & rngCurrent.Row & ":Q" & rngCurrent.Row).Font.Strikethrough = False
Range("M" & rngCurrent.Row).Interior.ColorIndex = 4
'Exit Sub
End If

 

[SS Rookie]

Try this version (I think I get what you're looking for)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("L:L"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
       
        Select Case Target.Value
            Case Is = Range("A16").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = True
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
            Case Is = Range("A15").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.ColorIndex = 4
            Case Else
                '???
        End Select
    End If
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Now it's not changing the cell color at any point.

 

[kevin9999]

Now it's not changing the cell color at any point.

When I use the code, it works for me:

SS Rookie.xlsm
	A	B					G	H	I	J	K	L	M	N	O	P	Q
1												HDR
2							TEST	TEST	TEST	TEST	TEST	A	TEST	TEST	TEST	TEST	TEST
3							TEST	TEST	TEST	TEST	TEST	A	TEST	TEST	TEST	TEST	TEST
4							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
5							TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
6							TEST	TEST	TEST	TEST	TEST	C	TEST	TEST	TEST	TEST	TEST
7							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
8							TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
9							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
10							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
11							TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
12							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
13							TEST	TEST	TEST	TEST	TEST	A	TEST	TEST	TEST	TEST	TEST
14							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
15	A						TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
16	B						TEST	TEST	TEST	TEST	TEST	A	TEST	TEST	TEST	TEST	TEST
17							TEST	TEST	TEST	TEST	TEST		TEST	TEST	TEST	TEST	TEST
18							TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
19							TEST	TEST	TEST	TEST	TEST	B	TEST	TEST	TEST	TEST	TEST
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
L2:L19	List	A,B,C

I can't do much more to get to the bottom of the problem unless I can get a copy of your actual sheet using the XL2BB - Excel Range to BBCode or better still, if you could share your file using Dropbox, Google Drive or similar file sharing site.

 

[SS Rookie]

I found the one issue since which was just from copy & paste. My SS reference cells are Ai5 and Ai6. It's still for some reason requiring me to reselect the drop-down cell to get the color to change and also to go away. Attached is a link to the actual sheet.

Test_M.xlsm

drive.google.com

 

[kevin9999]

Thank you for providing the file. I'm away from my usual laptop at the moment, but I will get to this in a few hours' time.

 

[kevin9999]

Turned out easier than I thought to figure out. The code in post #10 was Worksheet_Change code. For some reason you converted it to Worksheet_SelectionChange code. You do realise they're not the same thing? Anyhow, I changed it back to Worksheet_Change and it works just fine. File link here: Test_M.xlsm

Full code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("L:L"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        
        Select Case Target.Value
            Case Is = Range("AI6").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = True
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
            Case Is = Range("AI5").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.ColorIndex = 4
            Case Else
        End Select
    End If
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
    
End Sub

 

[kevin9999]

In hindsight, would be better with the Else option:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("L:L"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        
        Select Case Target.Value
            Case Is = Range("AI6").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = True
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
            Case Is = Range("AI5").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.ColorIndex = 4
            Case Else
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
        End Select
    End If
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
    
End Sub

 

[SS Rookie]

In hindsight, would be better with the Else option:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Range("L:L"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
       
        Select Case Target.Value
            Case Is = Range("AI6").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = True
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
            Case Is = Range("AI5").Value
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.ColorIndex = 4
            Case Else
                Range("B" & Target.Row & ":Q" & Target.Row).Font.Strikethrough = False
                Range("M" & Target.Row).Interior.Color = Range("L" & Target.Row).Interior.Color
        End Select
    End If
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
   
End Sub

Thanks, it is doing what I was going for. No, I'm not too keen on the differences between the Worksheet Change and WorksheetSelection Change. I'm also curious the need for the If Not at teh beginning of the code.

 

[kevin9999]

Thanks, it is doing what I was going for.

Glad to hear that we found a Solution to your problem. I knew we'd get there in the end

Regarding Worksheet Change vs WorksheetSelection Change, the first will initiate code running if a change happens in the value of a cell (except if it happens as a result of a formula - but that's another story) whereas the second will initiate code running if a different cell is merely selected (whether a change in value occurred or not).

The If Not...blah..blah...Is Nothing, is another way of saying If...blah..blah..Is Something or in other words, only continue with the code if a certain condition is met/true (e.g. a cell changes, in your case in column L) otherwise go straight to the End If line on the code.