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

 

[JackDanIce]

What is the error and on what line does it occur? What happens if e.g. cell H12 is changed? It's not clear what the change in Target cell is meant to trigger

 

[muhammad susanto]

There is no VBA event that can capture the change in colour of a cell. The best you can do is use some other event, or attach a macro to a button which you click to change colours.

Here's a sheet change macro that will loop through cells that you have pre-defined (in this example A1, B2, C3, D4) and populate them with an x if they are green, and remove the x if you remove the green colour. The macro will only run when you change something on a cell somewhere.

 

[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

that's code should be work, i think it's like reverse order..
i mean, it that cell filled green color (manually), that cell automatically filled X (turn off/on)

 

[muhammad susanto]

i hope this file attachment could help..
https://app.box.com/s/gta2hsathmm73sb6z9pytq2nru9nu5ch

 

[Fluff]

Hi
2 things
1) your file is an xlsx & therefore has no macros in it. You need to save it as an .xlsm file & then put your code in.
2) You are looking at 4 cells that don't have any colour. You need to change the cells that need to be checked like

    Set r = Range("M19, M21, U22, M25")

 

[muhammad susanto]

Hi
2 things
1) your file is an xlsx & therefore has no macros in it. You need to save it as an .xlsm file & then put your code in.
2) You are looking at 4 cells that don't have any colour. You need to change the cells that need to be checked like

    Set r = Range("M19, M21, U22, M25")

thank you Fluff, i have do you suggestion...but i don't see anything that change...

 

[muhammad susanto]

when i click / select green color in that cell, automatically the cell show X

 

[Fluff]

You have already explained in your post#3 that the code can't do that.

 

[muhammad susanto]

someone would help me, how that code works! Or have any idea?