automatically run a macro

Hello,
I have written a macro which operates by means of ctrl + Q. (This letter was
merely an arbitrary choice!)

The action is as follows:-
1. Enter new data in a cell then press <ENTER>
2. The data is then automatically compared with the contents of a cell on
different sheet
3. If the new cell data is greater than or equal in value to that data in
the cell on the other sheet then change the font colour of the new cell data
to red and display the difference between the two cells in a cell in another
column
4. Otherwise just display the new contents of the cell in a black font
colour as normal and do nothing else

I have the code to do all this in the macro but I want to make the code run
automatically when I press <ENTER> after updating the contents of any one of
several cells on a worksheet.

I don't know how to do this by means of an IF function, as it requires
several things to happen as the data in the cell is entered.

Can anyone tell me how to do it?

Thank you in advance for your kind help.

Steve

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"42410" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I have written a macro which operates by means of ctrl + Q. (This letter
was
> merely an arbitrary choice!)
>
> The action is as follows:-
> 1. Enter new data in a cell then press <ENTER>
> 2. The data is then automatically compared with the contents of a cell on
> different sheet
> 3. If the new cell data is greater than or equal in value to that data in
> the cell on the other sheet then change the font colour of the new cell
data
> to red and display the difference between the two cells in a cell in
another
> column
> 4. Otherwise just display the new contents of the cell in a black font
> colour as normal and do nothing else
>
> I have the code to do all this in the macro but I want to make the code
run
> automatically when I press <ENTER> after updating the contents of any one
of
> several cells on a worksheet.
>
> I don't know how to do this by means of an IF function, as it requires
> several things to happen as the data in the cell is entered.
>
> Can anyone tell me how to do it?
>
> Thank you in advance for your kind help.
>
> Steve
>
>

It might just be me being daft, but I don't see why a macro is needed at
all. Couldn't it be done with Conditional Formatting and a formula?
Ignore me if you like!
--
Andy.


"Bob Phillips" <[email protected]> wrote in message
news:%23e%[email protected]...
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> With Target
> 'do your stuff
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "42410" <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>> I have written a macro which operates by means of ctrl + Q. (This letter
> was
>> merely an arbitrary choice!)
>>
>> The action is as follows:-
>> 1. Enter new data in a cell then press <ENTER>
>> 2. The data is then automatically compared with the contents of a cell on
>> different sheet
>> 3. If the new cell data is greater than or equal in value to that data in
>> the cell on the other sheet then change the font colour of the new cell
> data
>> to red and display the difference between the two cells in a cell in
> another
>> column
>> 4. Otherwise just display the new contents of the cell in a black font
>> colour as normal and do nothing else
>>
>> I have the code to do all this in the macro but I want to make the code
> run
>> automatically when I press <ENTER> after updating the contents of any one
> of
>> several cells on a worksheet.
>>
>> I don't know how to do this by means of an IF function, as it requires
>> several things to happen as the data in the cell is entered.
>>
>> Can anyone tell me how to do it?
>>
>> Thank you in advance for your kind help.
>>
>> Steve
>>
>>
>
>

hi Steve

Right Click on Sheet Tab|View Code|

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("b1") Then
Call XXXX '( XXXX - macro name)
End If
End Sub

regards,
Suresh tp

"42410" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I have written a macro which operates by means of ctrl + Q. (This letter
was
> merely an arbitrary choice!)
>
> The action is as follows:-
> 1. Enter new data in a cell then press <ENTER>
> 2. The data is then automatically compared with the contents of a cell on
> different sheet
> 3. If the new cell data is greater than or equal in value to that data in
> the cell on the other sheet then change the font colour of the new cell
data
> to red and display the difference between the two cells in a cell in
another
> column
> 4. Otherwise just display the new contents of the cell in a black font
> colour as normal and do nothing else
>
> I have the code to do all this in the macro but I want to make the code
run
> automatically when I press <ENTER> after updating the contents of any one
of
> several cells on a worksheet.
>
> I don't know how to do this by means of an IF function, as it requires
> several things to happen as the data in the cell is entered.
>
> Can anyone tell me how to do it?
>
> Thank you in advance for your kind help.
>
> Steve
>
>

Hi Andy

i'm with you on this one

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
<Andy B> wrote in message news:[email protected]...
> It might just be me being daft, but I don't see why a macro is needed at
> all. Couldn't it be done with Conditional Formatting and a formula?
> Ignore me if you like!
> --
> Andy.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%23e%[email protected]...
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> On Error GoTo ws_exit:
>> Application.EnableEvents = False
>> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
>> With Target
>> 'do your stuff
>> End With
>> End If
>>
>> ws_exit:
>> Application.EnableEvents = True
>> End Sub
>>
>> 'This is worksheet event code, which means that it needs to be
>> 'placed in the appropriate worksheet code module, not a standard
>> 'code module. To do this, right-click on the sheet tab, select
>> 'the View Code option from the menu, and paste the code in.
>>
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "42410" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hello,
>>> I have written a macro which operates by means of ctrl + Q. (This letter
>> was
>>> merely an arbitrary choice!)
>>>
>>> The action is as follows:-
>>> 1. Enter new data in a cell then press <ENTER>
>>> 2. The data is then automatically compared with the contents of a cell
>>> on
>>> different sheet
>>> 3. If the new cell data is greater than or equal in value to that data
>>> in
>>> the cell on the other sheet then change the font colour of the new cell
>> data
>>> to red and display the difference between the two cells in a cell in
>> another
>>> column
>>> 4. Otherwise just display the new contents of the cell in a black font
>>> colour as normal and do nothing else
>>>
>>> I have the code to do all this in the macro but I want to make the code
>> run
>>> automatically when I press <ENTER> after updating the contents of any
>>> one
>> of
>>> several cells on a worksheet.
>>>
>>> I don't know how to do this by means of an IF function, as it requires
>>> several things to happen as the data in the cell is entered.
>>>
>>> Can anyone tell me how to do it?
>>>
>>> Thank you in advance for your kind help.
>>>
>>> Steve
>>>
>>>
>>
>>
>
>

That did it Bob! Thanks
Steve

"Bob Phillips" <[email protected]> wrote in message
news:#e#[email protected]...
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> With Target
> 'do your stuff
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "42410" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> > I have written a macro which operates by means of ctrl + Q. (This letter
> was
> > merely an arbitrary choice!)
> >
> > The action is as follows:-
> > 1. Enter new data in a cell then press <ENTER>
> > 2. The data is then automatically compared with the contents of a cell
on
> > different sheet
> > 3. If the new cell data is greater than or equal in value to that data
in
> > the cell on the other sheet then change the font colour of the new cell
> data
> > to red and display the difference between the two cells in a cell in
> another
> > column
> > 4. Otherwise just display the new contents of the cell in a black font
> > colour as normal and do nothing else
> >
> > I have the code to do all this in the macro but I want to make the code
> run
> > automatically when I press <ENTER> after updating the contents of any
one
> of
> > several cells on a worksheet.
> >
> > I don't know how to do this by means of an IF function, as it requires
> > several things to happen as the data in the cell is entered.
> >
> > Can anyone tell me how to do it?
> >
> > Thank you in advance for your kind help.
> >
> > Steve
> >
> >
>
>

obviously Steve isn't :-)

Bob

"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Andy
>
> i'm with you on this one
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> <Andy B> wrote in message news:[email protected]...
> > It might just be me being daft, but I don't see why a macro is needed at
> > all. Couldn't it be done with Conditional Formatting and a formula?
> > Ignore me if you like!
> > --
> > Andy.
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%23e%[email protected]...
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>
> >> On Error GoTo ws_exit:
> >> Application.EnableEvents = False
> >> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> >> With Target
> >> 'do your stuff
> >> End With
> >> End If
> >>
> >> ws_exit:
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >> 'This is worksheet event code, which means that it needs to be
> >> 'placed in the appropriate worksheet code module, not a standard
> >> 'code module. To do this, right-click on the sheet tab, select
> >> 'the View Code option from the menu, and paste the code in.
> >>
> >>
> >> --
> >>
> >> HTH
> >>
> >> RP
> >> (remove nothere from the email address if mailing direct)
> >>
> >>
> >> "42410" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> Hello,
> >>> I have written a macro which operates by means of ctrl + Q. (This
letter
> >> was
> >>> merely an arbitrary choice!)
> >>>
> >>> The action is as follows:-
> >>> 1. Enter new data in a cell then press <ENTER>
> >>> 2. The data is then automatically compared with the contents of a cell
> >>> on
> >>> different sheet
> >>> 3. If the new cell data is greater than or equal in value to that data
> >>> in
> >>> the cell on the other sheet then change the font colour of the new
cell
> >> data
> >>> to red and display the difference between the two cells in a cell in
> >> another
> >>> column
> >>> 4. Otherwise just display the new contents of the cell in a black font
> >>> colour as normal and do nothing else
> >>>
> >>> I have the code to do all this in the macro but I want to make the
code
> >> run
> >>> automatically when I press <ENTER> after updating the contents of any
> >>> one
> >> of
> >>> several cells on a worksheet.
> >>>
> >>> I don't know how to do this by means of an IF function, as it requires
> >>> several things to happen as the data in the cell is entered.
> >>>
> >>> Can anyone tell me how to do it?
> >>>
> >>> Thank you in advance for your kind help.
> >>>
> >>> Steve
> >>>
> >>>
> >>
> >>
> >
> >
>
>