Forum Discussion
Auto Fill Options unavailable
The code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyColor As Long
MyColor = RGB(224, 255, 224) ' a light green - change as desired
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = xlColorIndexNone
With ActiveCell
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.Color = MyColor
.EntireColumn.Interior.Color = MyColor
End With
Application.ScreenUpdating = True
End Sub
If you want to enable the Auto Fill Options menu, you can remove or modify the code that interferes with it. In this case, you can remove the Worksheet_SelectionChange event handler.
To remove the event handler follow these points
Open the Visual Basic for Applications (VBA) editor by pressing Alt+F11 in Excel.
In the Project Explorer window (if not visible, press Ctrl+R to show it), find the worksheet that contains the code.
Double-click on the worksheet to open its code module.
Delete the entire code within the module (the code you provided in your question).
After removing the code, the Auto Fill Options menu should reappear. If you need to retain the functionality provided by the code but still want to use the Auto Fill Options, you may need to modify the code to ensure it doesn't interfere with the menu.
Rahul-kumar I experience the same problem with Office 365's AutoFill: My code works 100% in Excel 2007. I have just renewed my Office 365 license on another computer and transferred the file to the computer with 365. The AutoFill works on Excel 2007, but not on 365: Exact same file, exact same code..? I checked the settings -all OK. When I test the AutoFill code with a new macro on a blank part of the same sheet, it works, but I just can't get it to work in the macro where it gives that Error 1004 AutoFill method failure..? What's funny, is that it does the AutoFill perfectly, but then give this error message after it did the autofill, and then stops the rest of the macro's code to execute..? I cannot understand why the code works 100% in an old 2007 version of Excel and not in the latest version..? It is like Microsoft evolution is going backwards..?
