How to auto run a macro in actived sheet?

[Guest]

Hello Excel professionals,
I have a sheet and a macro that I want that macro auto run when that sheet
is in active. I looked in Help for AutoRunMacro, but I don't know how to do
for this macro. My macro is just to change color in a correct cell when it
reads value from another cell that is like for example: $E$14. The cells to
be changed color are different, but each time is just one cell. Only one
cell is changed color. How do I do? Thank you in advance.
Tvnguye

 

[Don Guillett]

This is NOT very clear but you can do this sort of thing with a
worksheet_change event or a worksheet_selection event.

 

[Dave Peterson]

I don't think I'd use a macro to do this.

Have you considered format|Conditional formatting?

 

[Guest]

Hi Dave,
Thank you for your responses, but not help yet.
I have a macro like this. For example, in a Sheet2 I have a list box to
select a name in 4 names (from name1 to name4). Each name has a number.
These names and numbers are in Sheet3. I have a cell E90 in Sheet2 to show a
number of a name in list above. I used Vlookup in cell E90, and I hid this
cell from users. Each time I click in a list box to select a name, cell E90
will show a number. I have other cells like J3=1 (for name1), J4=3 (for
name2), J5=8 (for name3), J6=10 (for name4). These numbers are always shown
from J3 to J6. The background of Sheet2 is in a color. Each time I lick in
list box to select a name, for example: name1, cell E90 shows 1, and then I
want here is a in cells of column J will be highlighted background to light
blue. For example: (colorindex = 34). So the users will pay attention to
highlighted cell with a number.
ActiveCell.Interior.ColorIndex = 34
I have a macro to change background color of each cell of J, but I have to
run that macro by myself. I want that macro run automatically each time I
click a name in a list box. How do I do? Thanks.

 

[Guest]

Hi Don,
Thank you for your responses, but not help yet.
I have a macro like this. For example, in a Sheet2 I have a list box to
select a name in 4 names (from name1 to name4). Each name has a number.
These names and numbers are in Sheet3. I have a cell E90 in Sheet2 to show a
number of a name in list above. I used Vlookup in cell E90, and I hid this
cell from users. Each time I click in a list box to select a name, cell E90
will show a number. I have other cells like J3=1 (for name1), J4=3 (for
name2), J5=8 (for name3), J6=10 (for name4). These numbers are always shown
from J3 to J6. The background of Sheet2 is in a color. Each time I lick in
list box to select a name, for example: name1, cell E90 shows 1, and then I
want here is a in cells of column J will be highlighted background to light
blue. For example: (colorindex = 34). So the users will pay attention to
highlighted cell with a number.
ActiveCell.Interior.ColorIndex = 34
I have a macro to change background color of each cell of J, but I have to
run that macro by myself. I want that macro run automatically each time I
click a name in a list box. How do I do? Thanks.

 

[Dave Peterson]

Your listbox is from the Forms toolbar.

You can use a formula that uses that linked cell:
=if(e90="","",index(sheet1!a1:A4,e90))
(I used sheet1!a1:A4 is the home for the names)

Then I can use this cell in my format|conditional formatting dialog.

You may find that it's a little easier using data|validation. Then you can dump
the linked cell and the additional cell with the formula.

 

[Guest]

Hi Dave,
You misunderstood my question. I don't want to change value of J3:J6, but I
want to change background color of it when its value = E14. For example, if
E14 = 1, background color of J3 will be changed to light blue, because J3 is
always = 1. If E14 change to another number, J3 will return its old
background color, and another J will be changed background color. So I don't
know how to do yet. I just know how to run in a macro, but macro cannot be
auto run.
Thank you.

 

[Dave Peterson]

That sounds like you want to use format|Conditional formatting.

If you use another cell to show the value of the name selected in the listbox,
you can use that cell in the conditional formatting expression.

But I'm confused about where the formulas are, where the original list is and
where the cells to change colors are.

Maybe you could try again.

Hi Dave,
You misunderstood my question. I don't want to change value of J3:J6, but I
want to change background color of it when its value = E14. For example, if
E14 = 1, background color of J3 will be changed to light blue, because J3 is
always = 1. If E14 change to another number, J3 will return its old
background color, and another J will be changed background color. So I don't
know how to do yet. I just know how to run in a macro, but macro cannot be
auto run.
Thank you.