macro in function

[duckfoot]

Is it possible to run a macro as the result of an IF function?

i.e.
IF(logical_test,value_if_true,RUN MACRO1)

thanks

[s-o-s]

On 2002-05-01 04:35, duckfoot wrote:
Is it possible to run a macro as the result of an IF function?

i.e.
IF(logical_test,value_if_true,RUN MACRO1)

thanks

If you have created a UDF(User Defined Function) then yes.

It does not matter that Excel Already has this function just create a unique name

eg
Function My_If(Cells or Ranges to do test on)

If TestTrue then Call Macro1

End Function

If this is not specific enough Give me some more info.

[duckfoot]

i'm not entirely sure what you mean so i'll give you some more detail if thats ok:

this is the simple formula i'm using and the macro, when run, simply draws a thick line under several cells.

=IF(RC[1]="",RC[1],macro1)

[duckfoot]

in fact, that makes no sense to me whatsoever...

i don't know how to use a UDF and really need to know how i "call macro1"
or how i write: "run macro1" in the function.

[s-o-s]

It sounds like Conditional Formating may be what your looking for...

Select the 1st cell for the Test

Lets say it is Cell C3

Format/Conditional Formatting

In the first drop down change to show Formula is

In the next box enter

=isblank(C3)

Next click the format box and choose underline (This does not stand out so well as you cannot add think...you could also add a Gold background at the same time)
Click OK

Copy the formats to all the cells that you want testing. If the cell is Blank you will see Golden highlighted cells with underline.

Let me know if this does not solve you problem as this is much easier to do than the VB will be(especially if you are not too familiar with it).

[duckfoot]

ok ok... i've tried that and its not really working.
Imagine i have column A filled irregularly with values and with blank cells inbetween these. I need a thick line passing along the top of only the filled cells and across to column E.
I need to do this rapidly down the column for several thousand rows.
Really appreciate you taking the time to help
Thanks!

[s-o-s]

On 2002-05-01 05:31, duckfoot wrote:
ok ok... i've tried that and its not really working.
Imagine i have column A filled irregularly with values and with blank cells inbetween these. I need a thick line passing along the top of only the filled cells and across to column E.
I need to do this rapidly down the column for several thousand rows.
Really appreciate you taking the time to help
Thanks!

Can I try one more go at the conditional formatting as It will be much faster for you.

Select Columns A-E
Enter Conditional Formatting
In the box do formula is

=ISBLANK($A1)

Set formatting to Black line (And Gold? as it won't do a thick line)

Click OK.

This will give you instantly the whole spreadsheet highlighted in rows A-E where column A is blank.

If this still doesn't help let me know.

I should really be getting on with my day job soon though so I will give you the code tonight.

[duckfoot]

nope - sorry, but it really isn't happening for me...
I'm keen on this change event idea but don't know how to apply it to my problem. This is a link to an explanation to the same query:

http://www.cpearson.com/excel/excelM.htm#RunFromCell

but i don't know how to apply it to my problem.
cheers

[s-o-s]

OK I have now got the macros working.

I will use the change events...Drop me your email by Private message if you prefer and I'll send it out. (it will take 2-3) hours from now before I get to my home email though...

Anyone else interested drop me a line

If too many I'll post it on the web.

[s-o-s]

As promised... I have returned with the solution.

This link takes you to the file which you should save to your hard disk and then open from Excel directly...It will not perform correctly from the Web Page view of Excel.

http://<font color="blue">S-O-S File...derline blanks

Hope This Helps