SelectionChange (Single click to green [does stuff] then if already green back to nofill [does other stuff])

[Sleeplol]

Hello Everyone,

I'm fairly stuck on this.

Range J12:R30 has dynamic data so sometimes certain cells contain values other times they don't (and what not). All cells start off with no fill.
Current code:

1. If a selected cell contains a value then it turns green and runs a macro
2. If the selected cell is already green (macro has already ran) then the macro won't run
3. If no value in selected cell then macro won't run.
4. If no value in certain cells outside of Range J12:R30 then macro won't run

What I need:

1. To be able to select a green cell (only a green cell in Range J12:R30) and turn it back to nofill and then run a separate macro (we can call it "Remove post")
2. But macro "Remove post" can only run if the cell was already green and is now nofill

I really appreciate any help with this
Thanks

Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Selection.Count = 1 Then

     If Intersect(Target, Range("J12:R30")) Is Nothing Then Exit Sub
   
    If Worksheets("TrainingMgn").Range("E10") <> "" Then
    If Worksheets("TrainingMgn").Range("E12") <> "" Then
    If Target.Interior.Color = 16777215 Then
    Application.Calculation = xlAutomatic

    Call SetPosition
    
    If Worksheets("TrainingMgn").Range("K8") <> "" Then

    Call SendNewTrainingSpecsToPreTable1
    Target.Interior.Color = vbGreen
    
 End If
 End If
 End If
 End If

End If
End Sub

 

[Sleeplol]

Hello,

Well, I tinkered for a bit and figured I'd change my approach to:
Single click a cell within a range once for vbGreen; single click again for no fill
Then have a macro run if cell is green then another macro run if cell goes back to no fill

This is what I came up with that seems to answer my needs.
Perhaps this can help someone down the road.

If Not Intersect(Target, Range("J12:R30")) Is Nothing Then
Application.Calculation = xlAutomatic
    If Worksheets("TrainingMgn").Range("E10") <> "" Then
    If Worksheets("TrainingMgn").Range("E12") <> "" Then
    If IsEmpty(Target.Value) = False Then
    
If Target.Interior.ColorIndex = 4 Then
Target.Interior.ColorIndex = 0
    
    Call SetPositionToDelete
Else
Target.Interior.ColorIndex = 4
End If
End If
End If
End If
End If




If Not Intersect(Target, Range("J12:R30")) Is Nothing Then
If Target.Interior.ColorIndex = 4 Then

    Call SetPosition
    If Worksheets("TrainingMgn").Range("K8") <> "" Then

        Call SendNewTrainingSpecsToPreTable1

End If
End If
End If



If Not Intersect(Target, Range("J12:R30")) Is Nothing Then
If Target.Interior.ColorIndex = 0 Then

End If
End If



End If

 

[BSALV]

i think it can be reduced to

If Intersect(Target, Range("J12:R30")) Is Nothing Then Exit Sub
Application.Calculation = xlAutomatic

If Worksheets("TrainingMgn").Range("E10") <> "" And Worksheets("TrainingMgn").Range("E12") <> "" And IsEmpty(Target.Value) = False Then
     If Target.Interior.ColorIndex = 4 Then
          Target.Interior.ColorIndex = 0
          Call SetPositionToDelete
          MsgBox "if you wanted to do something else at the end ???"
     Else
          Target.Interior.ColorIndex = 4
          Call SetPosition
          If Worksheets("TrainingMgn").Range("K8") <> "" Then Call SendNewTrainingSpecsToPreTable1
     End If
End If

 

[BSALV]

or this

If Intersect(target, Range("J12:R30")) Is Nothing Then Exit Sub
Application.Calculation = xlAutomatic

If Worksheets("TrainingMgn").Range("E10") <> "" And Worksheets("TrainingMgn").Range("E12") <> "" And IsEmpty(target.Value) = False Then
     Call SetPositionToDelete
     b = (target.Interior.ColorIndex = 4)                       'flag what color
     target.Interior.ColorIndex = IIf(b, 0, 4)                  'flipflop color
     If b Then
          MsgBox "if you wanted to do something else at the end ???"
     Else
          If Worksheets("TrainingMgn").Range("K8") <> "" Then Call SendNewTrainingSpecsToPreTable1
     End If
End If

 

[Sleeplol]

Hi BSALV
Your first bit of code works very nicely.
Thanks for taking the time to work this out.

I'll use your code in my project because it's so much cleaner than mine.

Thanks

 

[BSALV]

you're welcome