Macro Code Not Work

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,089
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hello all...

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

somebody would help me :

this code :
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
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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.
 
Upvote 0
hello all...

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

somebody would help me :

this code :
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)
 
Upvote 0
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
Code:
    Set r = Range("M19, M21, U22, M25")
 
Upvote 0
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
Code:
    Set r = Range("M19, M21, U22, M25")

thank you Fluff, i have do you suggestion...but i don't see anything that change...
 
Upvote 0
when i click / select green color in that cell, automatically the cell show X
 
Upvote 0
You have already explained in your post#3 that the code can't do that.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top