macro_change font style

**tommy_gtr** · 08-09-2005, 08:06 AM

hi,

i'm a beginner in macros and now i have a little problem, which is very easy, but not for me....so please help me....

so i'm trying to write a macro, which will change font style in some cells to "strikethrough", when i type in other cell "ok".

I wrote a very simply macro and it really works, but I must run it after i type "ok" in cell to make affect. is there any way to make it automatically....

Thanks

tommy

**dominicb** · 08-09-2005, 08:18 AM

Good afternoon Tommy_gtr

Welcome to the world of event procedures!

You haven't given much background information about your project, so I've assumed that your strikethrough macro is named macro1, and that you want to type OK in cell A1.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("A1").Value = "OK" Then Call macro1
End Sub

The code above will need copying into the ThisWorkbook pane within your VBE and will call macro1 whenever "OK" is inserted into A1. For more info on event procedures have a look here:

http://www.cpearson.com/excel/events.htm

HTH

DominicB

**tommy_gtr** · 08-09-2005, 08:51 AM

thank you for your answer,

but I'm a really beginner and I tried your code....it works, but first I must run your macro and then it call my "change font" macro.

but I'm looking for solution, which automatically change my font always when I typed "ok" in cell (for example in A1).

maybe I pasted your code on incorrect place.

If you have patience with me, please help....

Thanx

Tom Ogilvy · 08-09-2005, 10:05 AM

Did you check out the link at Chip Pearson's site where this is explained in
pretty good detail?

--
Regards,
Tom Ogilvy

"tommy_gtr" <[email protected]> wrote
in message news:[email protected]...
>
> thank you for your answer,
>
> but I'm a really beginner and I tried your code....it works, but first
> I must run your macro and then it call my "change font" macro.
>
> but I'm looking for solution, which automatically change my font always
> when I typed "ok" in cell (for example in A1).
>
> maybe I pasted your code on incorrect place.
>
> If you have patience with me, please help....
>
> Thanx
>
>
> --
> tommy_gtr
> ------------------------------------------------------------------------
> tommy_gtr's Profile:
http://www.excelforum.com/member.php...o&userid=26089
> View this thread: http://www.excelforum.com/showthread...hreadid=394180
>

**dominicb** · 08-09-2005, 02:35 PM

Hi Tommy_gtr

The point about an event procedure is it run's itself at a prespecified event - in this case someone changing a workbook. It doesn't need your intervention.

Have a look at the picture attached. Does that help?

DominicB

**tommy_gtr** · 08-10-2005, 03:09 AM

Dear dominicb,

Thank you for your help....now it really works :-)

I'm a new in writing macros, but I need in very short time learn it.
I know that it's not so easy....
Can you recommend me some good links, books, etc.

I will appreciate your every answer.

Have a nice day :-)

**dominicb** · 08-10-2005, 04:11 AM

Hi Tommy_gtr

I have found the links below helpful (listed in no particular order) with lots of examples on VBA and how to apply it - but that's not to say that there aren't hundreds of other sites available to help you learn.

http://www.cpearson.com/excel/topic.htm
http://j-walk.com/ss/
http://www.contextures.com

John Walkenbach has written a number of books about VBA, aimed at both the beginner and the advanced user, so you might like to get one of those, availabe from his site above, or usually cheaper from Amazon. The books usually come with a CD with plenty of examples on.

Also, try and use the examples you come across in your own projects because there's no substitute for practice and expreience.

HTH

DominicB

**tommy_gtr** · 08-10-2005, 05:44 AM

Hi,

thanks.... I'll check all links.

but I'm sure that it's not so easy way which I have chosen.
I have still problem with my little macro ....

I type "ok" and it's change may font style, but only in absolute way.
I'm trying to write it relatively but till now I'm not very succesful.

This is my code:

Sub Macro 1()

ActiveCell.Offset(-1, -9).Range("A1:I1").Select
With Selection.Font
.Name = "Arial CE"
.Size = 9
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 9).Range("A1").Select
End Sub

and I call it with your macro (which I need to write in a relative way (I think)):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("L41").Value = "OK" Then Call Macro 1
End Sub

**tommy_gtr** · 08-10-2005, 09:31 AM

ok,

I worked on my macro and status is:

Sub strikeout()
'
' strikeout Makro
' Makro zaznamenané 10.8.2005, IM
'

ActiveCell.Offset(-1, -9).Range("A1:I1").Select
With Selection.Font
.Name = "Arial CE"
.Size = 9
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(1, 9).Range("A1").Select

End Sub

and call:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = "OK" Then Call strikeout
End Sub

but now it doesn't depend on where I type my "ok"..... an it's a problem....cause I have one specific column in which I want to type ok.