Change EventClosed

Question

Hello,
I want to run a macro when certain cells are changed on every worksheet of a workbook.  I copied the Change Event  Example from VBA Help:

Private Sub Workbook_SheetChange(ByVal Sheet1 As Object, _
        ByVal A5 As Range)
    Font.ColorIndex = 5        
    ' runs when a sheet is changed
End Sub

but it doesn't run.    Do I have to enable it in some way?

Thanks in advance,

Michael

Excelguru · Answer

Hi Mike

Try this 


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" And Target.Address = "$A$5" Then Target.Font.ColorIndex = 5
End Sub

Excelguru · Answer

Hello Mike

You didnt tell me which cells have to be changed. From the macro code you put I inferred that you have to change the cell colour of Cell A5 of sheet1
If you have more cells add more lines in the strcuture 
If Sh.Name = "Sheet1" And Target.Address = "$A$5" Then Target.Font.ColorIndex = 5 
where you have to change the sheet name and cell address
if you want all cells to change color just put 
Target.Font.ColorIndex = 5

Excelguru · Answer

Hello Mike

Then use this 
If Sh.Name = "Sheet1" And Target.Address = "$A$5" Then sheets("sheet2").range("f5").Font.ColorIndex = 5

Mike · Answer

OK That worked, but now I have another problem.  The computer is hooked up to a scale which sends the weights to a default Excel workbook which is unusable for Production, so I linked another workbook to it so that the weights will appear there.  The problem is that when the weights come over, they won't trigger the macro.  Any ideas?

Thanks in advance Excelguru,

Mike

Change Event

4 responses

Similar discussions

Subscribe To Our Newsletter!