Double click => returns different message boxes depending on cell color?

[Ayra88]

Dear friends,

I'm quite new to VBA and have been confronted with a difficult task at my job - I have been struggling with this for some hours, I hope you could help me. I'll try to make the problem simple:

Say, I have in column A various projects with different colors representing the risk of the projects.

I want to write a VBA Code that will perform the following:

If I double click on a green project => A message box should appear containing the text "Low Risk"
If I double click on a yellow project => A message box should appear containing the text "Medium Risk"
If I double click on an orange project => A message box should appear containing the text "High Risk"

I have formatted the cells with pastel colors, in the column C we can see the color codes of the cells.

Please see my code below. The Problem is, it doesn't matter on which cell I click, only the message box "Low Risk" will appear. I don't quite get where is the mistake.

I would very much appreciate your help!

(In reality the problem would be: double click on green cell => jump to another sheet ( I already have this part written); double click on yellow cell => message box "...."; double click on orange cell =>message box "...")

Best Regards, A.

---

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As rangE, Cancel As Boolean)
Dim Cell As range
For Each Cell In range("A1:A15")
With Cell
If Not Intersect(Target, range("A1:A15")) Is Nothing Then
If Cell.Interior.Color = 11854022 Then
Call showGreen
Exit For
ElseIf Cell.Interior.Color = 10092543 Then
Call showyellow
Exit For
ElseIf Cell.Interior.Color = 11389944 Then
Call showRed
Exit For
End If
End If
End With

Next Cell
End Sub
---
Sub showGreen()
Dim showCheck
showCheck = MsgBox("Low Risk")
End Sub
---
Sub showyellow()
Dim showCheck
showCheck = MsgBox("Medium Risk")
End Sub
---
Sub showRed()
Dim showCheck
showCheck = MsgBox("High Risk")
End Sub

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("A1:A15")) Is Nothing Then
      If Target.Interior.Color = 11854022 Then
         Call showGreen
      ElseIf Target.Interior.Color = 10092543 Then
         Call showyellow
      ElseIf Target.Interior.Color = 11389944 Then
         Call showRed
      End If
      Cancel = True
   End If
End Sub

 

[Ayra88]

Dear Fluff,

wow it works perfect! Thank you very very much! May I ask what does the line "Cancel = True" mean?

 

[Fluff]

It prevents the cell from going into edit mode.