Double Click Run Macro

[JoeExcelHelp]

I have the following code embedded in a sheet labeled "AO RA Skills" and its run by the following button code.. Is it possible to run code "AOColor" by double clicking cell A1 in that sheet so I could get rid of the button code?

Code: Select all

Sub AOColorEm()
    Dim cel As Range
    Dim varColor As Variant
    Application.ScreenUpdating = False
    ' Reset color
    Range("D5:K2000").Interior.ColorIndex = xlColorIndexNone
    ' Set color
    For Each cel In Range("D5:K5000").SpecialCells(xlCellTypeConstants)
        varColor = Application.VLookup(cel.Value, Range("N5:O100"), 2, False)
        If VarType(varColor) = vbDouble Then
            cel.Interior.ColorIndex = varColor
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

Code: Select all

Private Sub CommandButton22_Click()
Call AOColorEm
End Sub

[HansV]

Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        ' Cancel the default action
        Cancel = True
        ' Call the macro
        Call AOColorEm
    End If
End Sub

[JoeExcelHelp]

I get a run time error 1004 with this line highlighted

Code: Select all

For Each cel In Range("D5:K5000").SpecialCells(xlCellTypeConstants)

[Rudi]

What does the run-time error description say?
Maybe "No cells were found."? Any other message?

[JoeExcelHelp]

Sorry Hans and Rudi.. I didnt have any data in those cells.. really stupid mistake :)

[Rudi]

LOL... we all make those.
Cheers