Macro Code Not Work

[muhammad susanto]

hello all...

i have macro code, but i dont know it works...i have try but still error..

somebody would help me :

this code :

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">    Dim r As Range, rcell As Range
    Set r = Range("A1, B2, C3, D4")
    Application.EnableEvents = False
    For Each rcell In r
        If rcell.Interior.ColorIndex = 14 Then
            rcell = "X"
        Else
            rcell = ""
            
        End If
    Next rcell
    Application.EnableEvents = True
 </code>End Sub

anyone help me is appreciated...

m.susanto

 

[Jaafar Tribak]

Try this and see if it works for you :

Code goes in the ThisWorkbook Module :

Option Explicit

Private WithEvents oCmndBars As CommandBars


Private Sub Workbook_Open()
    Set oCmndBars = Application.CommandBars
End Sub


Private Sub oCmndBars_OnUpdate()
    RunMacro
End Sub


Private Sub RunMacro()
    Dim r As Range, rcell As Range
    
    Set r = Range("A1, B2, C3, D4")
    Application.EnableEvents = False
    For Each rcell In r
        If rcell.Interior.ColorIndex = 14 Then
            If rcell <> "X" Then
                rcell = "X"
            End If
        Else
            If rcell <> "" Then
                rcell = ""
            End If
        End If
    Next rcell
    Application.EnableEvents = True
 End Sub

The code should get activated after re-opening the workbook or by running the Workbook_Open event routine.