Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
I have the following two formulas for the same area the first works the
second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Just use
=C6="MCR" assuming that C6 is the active cell, and copy to the rest, Excel will adjust it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Perhaps i'm misunderstanding what active cell means, I don't want that to be
the choice in only that one cell, i want it to look in a group of cells and where ever it find mcr, i want the mcr to be turned red. I'm going to try what you gave me and i'll write you back with my sucess. thanks. -- NealMed "Bob Phillips" wrote: Just use =C6="MCR" assuming that C6 is the active cell, and copy to the rest, Excel will adjust it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
That is what I gave you. I mentioned active cell as that is what you will
use in place of C6. Excel will automatically update the formula for each cell. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... Perhaps i'm misunderstanding what active cell means, I don't want that to be the choice in only that one cell, i want it to look in a group of cells and where ever it find mcr, i want the mcr to be turned red. I'm going to try what you gave me and i'll write you back with my sucess. thanks. -- NealMed "Bob Phillips" wrote: Just use =C6="MCR" assuming that C6 is the active cell, and copy to the rest, Excel will adjust it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Bob, Thanks, I understand what you were trying to tell me now.
It works mostly the way i want, but i've been playing with it a little further. there will normally be a name or something associated with the mcr in the same cell. like todd mcr, or chris mcr, in the cell, How do i get it to not care about the name in the cell, just the incidence of mcr? -- NealMed "Bob Phillips" wrote: That is what I gave you. I mentioned active cell as that is what you will use in place of C6. Excel will automatically update the formula for each cell. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... Perhaps i'm misunderstanding what active cell means, I don't want that to be the choice in only that one cell, i want it to look in a group of cells and where ever it find mcr, i want the mcr to be turned red. I'm going to try what you gave me and i'll write you back with my sucess. thanks. -- NealMed "Bob Phillips" wrote: Just use =C6="MCR" assuming that C6 is the active cell, and copy to the rest, Excel will adjust it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
=ISNUMBER(FIND("MCR",C6))
should do it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... Bob, Thanks, I understand what you were trying to tell me now. It works mostly the way i want, but i've been playing with it a little further. there will normally be a name or something associated with the mcr in the same cell. like todd mcr, or chris mcr, in the cell, How do i get it to not care about the name in the cell, just the incidence of mcr? -- NealMed "Bob Phillips" wrote: That is what I gave you. I mentioned active cell as that is what you will use in place of C6. Excel will automatically update the formula for each cell. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... Perhaps i'm misunderstanding what active cell means, I don't want that to be the choice in only that one cell, i want it to look in a group of cells and where ever it find mcr, i want the mcr to be turned red. I'm going to try what you gave me and i'll write you back with my sucess. thanks. -- NealMed "Bob Phillips" wrote: Just use =C6="MCR" assuming that C6 is the active cell, and copy to the rest, Excel will adjust it -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NealMed" wrote in message ... I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Hi Neal,
If you use a formula then it must be one that will evaluate to either true or false. In your first one that is what is happening. It is saying if C$5 is less than ROW()-5 then format. another Example:- =FIND("MCR",C6)0 The FIND function returns the number of times the value was found. If it returns a number greater than zero then it is true so apply format. If it returns zero then it was false so no formatting. The above formula would only be used if the string is to be found somewhere in another string line ABCMCRXYZ although it works equally well if only MCR is in the cell. If you want conditional format based on the value of a cell than you do not need the formula. In xl2007: Select 'Format only cells that contain' and then 'Cell Value' and next box select 'Equal to' and third box enter the value MCR. (No equal signs or quotes.) In pre xl2007 select Cell Value is and then next box select 'Equal to' and then third box enter MCR. (No equal signs or quotes.) Regards, OssieMac "NealMed" wrote: I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Following your example does it work the same in Excel 2002, that is the
version that i use at work, they use office xp. -- NealMed "OssieMac" wrote: Hi Neal, If you use a formula then it must be one that will evaluate to either true or false. In your first one that is what is happening. It is saying if C$5 is less than ROW()-5 then format. another Example:- =FIND("MCR",C6)0 The FIND function returns the number of times the value was found. If it returns a number greater than zero then it is true so apply format. If it returns zero then it was false so no formatting. The above formula would only be used if the string is to be found somewhere in another string line ABCMCRXYZ although it works equally well if only MCR is in the cell. If you want conditional format based on the value of a cell than you do not need the formula. In xl2007: Select 'Format only cells that contain' and then 'Cell Value' and next box select 'Equal to' and third box enter the value MCR. (No equal signs or quotes.) In pre xl2007 select Cell Value is and then next box select 'Equal to' and then third box enter MCR. (No equal signs or quotes.) Regards, OssieMac "NealMed" wrote: I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Hi again Neal,
Yes it is OK with xl2002 (Office XP). That is the reason that I included instructions for xl2007 and pre xl2007 versions. Just as a suggestion, it is always a good idea to include the version of xl when posting questions because it can make a difference to how it might be answered. Regards, OssieMac "NealMed" wrote: Following your example does it work the same in Excel 2002, that is the version that i use at work, they use office xp. -- NealMed "OssieMac" wrote: Hi Neal, If you use a formula then it must be one that will evaluate to either true or false. In your first one that is what is happening. It is saying if C$5 is less than ROW()-5 then format. another Example:- =FIND("MCR",C6)0 The FIND function returns the number of times the value was found. If it returns a number greater than zero then it is true so apply format. If it returns zero then it was false so no formatting. The above formula would only be used if the string is to be found somewhere in another string line ABCMCRXYZ although it works equally well if only MCR is in the cell. If you want conditional format based on the value of a cell than you do not need the formula. In xl2007: Select 'Format only cells that contain' and then 'Cell Value' and next box select 'Equal to' and third box enter the value MCR. (No equal signs or quotes.) In pre xl2007 select Cell Value is and then next box select 'Equal to' and then third box enter MCR. (No equal signs or quotes.) Regards, OssieMac "NealMed" wrote: I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
especially as 2007 is so different.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "OssieMac" wrote in message ... Hi again Neal, Yes it is OK with xl2002 (Office XP). That is the reason that I included instructions for xl2007 and pre xl2007 versions. Just as a suggestion, it is always a good idea to include the version of xl when posting questions because it can make a difference to how it might be answered. Regards, OssieMac "NealMed" wrote: Following your example does it work the same in Excel 2002, that is the version that i use at work, they use office xp. -- NealMed "OssieMac" wrote: Hi Neal, If you use a formula then it must be one that will evaluate to either true or false. In your first one that is what is happening. It is saying if C$5 is less than ROW()-5 then format. another Example:- =FIND("MCR",C6)0 The FIND function returns the number of times the value was found. If it returns a number greater than zero then it is true so apply format. If it returns zero then it was false so no formatting. The above formula would only be used if the string is to be found somewhere in another string line ABCMCRXYZ although it works equally well if only MCR is in the cell. If you want conditional format based on the value of a cell than you do not need the formula. In xl2007: Select 'Format only cells that contain' and then 'Cell Value' and next box select 'Equal to' and third box enter the value MCR. (No equal signs or quotes.) In pre xl2007 select Cell Value is and then next box select 'Equal to' and then third box enter MCR. (No equal signs or quotes.) Regards, OssieMac "NealMed" wrote: I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
i now understand what you are explaining, I did try to place that formula
into my worksheet, however when i place the mcr into any of the cells, nothing changes. I'm going back to the excel for dummies book. LOL, just kidding. what am I missing? -- NealMed "OssieMac" wrote: Hi again Neal, Yes it is OK with xl2002 (Office XP). That is the reason that I included instructions for xl2007 and pre xl2007 versions. Just as a suggestion, it is always a good idea to include the version of xl when posting questions because it can make a difference to how it might be answered. Regards, OssieMac "NealMed" wrote: Following your example does it work the same in Excel 2002, that is the version that i use at work, they use office xp. -- NealMed "OssieMac" wrote: Hi Neal, If you use a formula then it must be one that will evaluate to either true or false. In your first one that is what is happening. It is saying if C$5 is less than ROW()-5 then format. another Example:- =FIND("MCR",C6)0 The FIND function returns the number of times the value was found. If it returns a number greater than zero then it is true so apply format. If it returns zero then it was false so no formatting. The above formula would only be used if the string is to be found somewhere in another string line ABCMCRXYZ although it works equally well if only MCR is in the cell. If you want conditional format based on the value of a cell than you do not need the formula. In xl2007: Select 'Format only cells that contain' and then 'Cell Value' and next box select 'Equal to' and third box enter the value MCR. (No equal signs or quotes.) In pre xl2007 select Cell Value is and then next box select 'Equal to' and then third box enter MCR. (No equal signs or quotes.) Regards, OssieMac "NealMed" wrote: I have the following two formulas for the same area the first works the second does not. =C$5<ROW()-5 =C$6:$I$14="MCR" The first one places a color in a group of cells unless a drop down menu gives it a different number of cells to cover. What i want the 2nd to do, is to have in that same grouping of cells, change the color of the font to red if the letters MCR are in that cell. What did i do wrong in my formula. Thanks for your help. Neal. -- NealMed |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help needed with cell color
Hi Again Neal,
Step by step guide to format if cell equals a particular value (MCR):- Select the range of cells where you want the conditional formatting. Select menu item Format, Conditional Formatting. Condition 1 leave default option Cell Value Is. Next box click drop down arrow and select equal to. Next box enter MCR. Click Format button. Select tab for type of formatting (Font, Border or Patterns) Set the format required and click OK. Click OK again. Note: The above is NOT case sensitive. Step by step guide to format if MCR is contained in cell (AMCRZ):- Select the range of cells where you want the conditional formatting. Select menu item Format, Conditional Formatting. Condition 1: Click drop down arrow and select Formula Is. Next box enter =FIND(MCR,A1,1)0. (Note: A1 is the first cell of the selected range) Click Format button. Select tab for type of formatting (Font, Border or Patterns) Set the format required and click OK. Click OK again. Note: The above formula IS case sensitive. The above formula can be replaced by the following for NOT case sensitive:- =SEARCH(MCR,A1,1)0 (Note: A1 is the first cell of the selected range) Hope this helps, Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions | |||
Default Border, Font Color, and Cell Background Color | Excel Discussion (Misc queries) |