Macros in a cell function

[Guest]

Can I run a macro from a cell function in Excel 2003 with the idea of
comparing 1 value against another and if its true, it gets highlighted yellow?

Thanks

 

[macropod]

Hi Redragon,

This sounds like a case for conditional formatting (i.e.
Format|Cells|Conditional Formatting), which allows you to specify up to
three formats in addition to the cell's underlying format, depending on
whether the conditions you specify are met. No vba required.

Cheers

 

[Guest]

Thanks for replying, but I've already tried the condition al formatting and
for my project 3 conditions is not enough, any ideas?

Thanks

 

[Otto Moehrbach]

If you can't use Conditional Format for your problem, then you will have to
use VBA. But you cannot get a cell function/formula to run a macro. There
are a number of ways to run a macro, even automatically, but you don't
provide enough information. Post back and tell us what you are trying to do
with more detail this time. HTH Otto

 

[Guest]

Well, I want to compare 6 numbers against another 6 numbers which are set. So
I want the first number to check against all 6 of the set numbers and then
the second number to check all the set numbers and so on, and if they match I
want the number to highlight in yellow. So if we say: -

Set Chosen
3 6 This 6 is in the 'Set' Number list
6 10
12 14
13 17 This 17 is in the 'Set' number list
17 20
21 27

So basically I want the 6 and 17 in the 'Chosen' list to be highlighted in
yellow.
Are you with me? And any ideas if so?

Thanks

 

[broro183]

Hi,
You've tried conditional formatting & say that 3 conditions is not
enough, but if you have used all 3 conditions to solve this request -
then there will be enough as this can be done with a single condition
in conditional formatting, ie:
=ISNUMBER(MATCH(B2,$A$2:$A$7,0))

However, if you are already using the 3 conditions for other
formatting, have a look at (& the linked pages):
http://www.contextures.com/xladvfilter01.html

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Guest]

Hi again, sorry to be a pain, but could you explain to me how that function
and each part of it works: -

=ISNUMBER(MATCH(B2,$A$2:$A$7,0))

Thanks ever so much!

 

[macropod]

Hi RedDragon,

The problem you've described accounts for only one condition, so Conditional
Formatting is a candidate.

Rob's formula (for cell B2) uses the MATCH function to test whether the
value in the cell being tested is found in the test range (A2:A7), whilst
the 0 in the function specifies that an exact match is required. When the
MATCH function gets a 'hit' it returns the offset to the first match and,
the 0 causes it to return 'N/A' if there is no match. The ISNUMBER function
then tests the MATCH function's results for the presence of any number, and
returns 'TRUE' if MATCH has returned a number. Inserted into a Conditional
Formatting formula dialogue box, the 'TRUE' result then drives the
formatting.

If you place Rob's formula in cell C2, instead of using it as a conditional
formatting formula, and copy it down, you'll get an idea of what it's doing.

Cheers

 

[broro183]

hi all,

RedDragon, does that clarify the function for you?

Macropod, Thanks for the concise explanation. Just out of curiosity,
would you have used Match, or another function (eg vlookup, countif
etc)?

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[macropod]

Hi Rob,

A COUNTIF function would probably have been the simplest to implement. For
example:
=COUNTIF($A$2:$A$7,B2)>0

Cheers

 

[broro183]

Hi Macropod,

ahhh, of course.
Thanks,

Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Guest]

Excellent, thanks to you all. You've been incredibly helpful. Thanks ever so
much.
Could someone just explain why the countif function is simpler then the
isnumber function please?

Thanks again

 

[broro183]

Hi Redragon,

no problem, thanks for the feedback

We have said that the countif function is simpler because it just
involves the use of a single function (ie "countif") rather than using
two different functions together as my solution did (ie "isnumber" &
"match").

Also, it looks neater/shorter, ie consider
=COUNTIF($A$2:$A$7,B2)>0
compared to,
=ISNUMBER(MATCH(B2,$A$2:$A$7,0))

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Guest]

Oh right I see, so basically it's just using the one function instead of the
2. Simple enough. Thanks again to all of you!!

 

[macropod]

Hi Redragon,

If you had a myriad of these in a worksheet, I think you'd find also that
Excel would recalculate much faster with the COUNTIF version.

Cheers