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!

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" wrote:

> 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!

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

"Mike H" wrote:

> 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" wrote:
>
> > 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!

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