Changing range color using Workbook_BeforeClose

Hi everyone,

I'm relatively new to Exce VBA programming. I'm trying to do something that I thought would be relatively simple. I want to use the Workbook_BeforeClose event to change the fill color of a range of cells before Excel closes. My code works fine if Excel is closed using either the standard "exit button" in the upper right hand corner or the File-Exit method. However, I also want to be able to exit using a separate macro located either on "Sheet1" or in a module. When I close Excel using the separate code, the Workbook_BeforeClose event fires normally but I get an error involving the code line containing Interior.ColorIndex. I don't understand why the code works one way and not the other. I would appreciate any help that I can get. My code is as follows:

Thanks, efddale

Module code:

Sub Program_Exit()
Application.Quit
End Sub

Before_Close code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim TestRange As Range
Set TestRange = Range("A1:J1")
TestRange.Interior.ColorIndex = 6

Hi everyone,

I'm relatively new to Exce VBA programming. I'm trying to do something that I thought would be relatively simple. I want to use the Workbook_BeforeClose event to change the fill color of a range of cells before Excel closes. My code works fine if Excel is closed using either the standard "exit button" in the upper right hand corner or the File-Exit method. However, I also want to be able to exit using a separate macro located either on "Sheet1" or in a module. When I close Excel using the separate code, the Workbook_BeforeClose event fires normally but I get an error involving the code line containing Interior.ColorIndex. I don't understand why the code works one way and not the other. I would appreciate any help that I can get. My code is as follows:

Thanks, efddale

Module code:

Sub Program_Exit()
Application.Quit
End Sub

Before_Close code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim TestRange As Range
Set TestRange = Range("A1:J1")
TestRange.Interior.ColorIndex = 6



I put this code into both the Workbook_BeforeClose event.................

Sheets("Sheet1").Select

Dim Testrange As Range
Set Testrange = Range("A1:J1")

Testrange.Interior.ColorIndex = 6

ActiveWorkbook.Save


And this code into a module.....................


Sheets("Sheet1").Select

Dim Testrange As Range
Set Testrange = Range("A1:J1")

Testrange.Interior.ColorIndex = 6

ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Quit

and it works what ever close method I use. (I put the save command in there so I wouldn't get the dialog asking me to save, but take it out if it doesn'e suit your needs)