Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 "Redragon" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
Thanks for replying, but I've already tried the condition al formatting and
for my project 3 conditions is not enough, any ideas? Thanks "macropod" wrote: 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 "Redragon" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 "Redragon" wrote in message ... Thanks for replying, but I've already tried the condition al formatting and for my project 3 conditions is not enough, any ideas? Thanks "macropod" wrote: 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 "Redragon" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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! :) "broro183" wrote: 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 "Redragon" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
Hi Rob,
A COUNTIF function would probably have been the simplest to implement. For example: =COUNTIF($A$2:$A$7,B2)0 Cheers "broro183" wrote in message ... 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
Hi Macropod, ahhh, of course. Thanks, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 :D |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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!! :D "broro183" wrote: 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros in a cell function
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 "Redragon" wrote in message ... 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!! :D "broro183" wrote: 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=537442 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading Cell Function??? | Excel Worksheet Functions | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
Function making cell really "empty" | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |