Click Colored Cell -> UserForm.Show Question.

[Jim]

Hi, I have another question, Im new at this and this site has been very very
helpful, wondering if anyone might know....

Is there a way to bring up a UserForm based only on if the user clicks on a
certain colored cell (say ColorIndex = 21)? I dont want to pre-set a range
because it is different from sheet to sheet. (Although setting entire sheet
range(A1:ZZ66500) would be ok i guess). THANKS!

 

[Mike H]

Jim

You obviously see a need for this, my view; FWIW, is that it would be a
pest but perhaps that's just me. This works on all sheets and if a cell with
colorindex 21 is selected a userform will pop up. You will of course need to
change the name of the userform to your name.

Alt+F11 to open VB editor. Double click 'This Workbook' and paste this in on
the right

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Interior.ColorIndex = 21 Then
UserForm1.Show
End If
End Sub

Mike

 

[Jim]

Thanks for the reply Mike! That worked perfect. I have trouble with the ByVal
stuff. Still a Novice. This helps out so much and makes my code pretty simple

 

[James]

ok, this works fine if i have a single cell colored, but not if i have a few
merged cells or if i select more than one cell at a time. I get a Runtime
error type 13, "Type mismatch" at the Selection.Value part. Does anyone know
why im getting this. I know merged cells may be a problem, but i get the same
error when i select multiple cells. here is my code. Thanks

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If Target.Interior.ColorIndex = 36 Then
If Selection.Value = "" Then 'IF BLANK YELLOW CELL
CLICKED
UserForm1.Show
ElseIf Selection.Value <> "" Then 'IF <> "" YELLOW CELL
CLICKED
UserForm1.Show
End If

End If
End Sub