|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
CF help needed with cell color
Ossie,
I thank you for your help. I had figured out the 1st one, that is sensitive to how it's written, the latter, Even if i copy the formula as written into a blank page, gives me an error. I'm not typing, just copy and pasting, to see what happens. I can't find any problem with what you wrote, it reads fine to me, can you help me trouble shoot it. I did try to change the A1, to C6 because that is the first active cell of use, but I still get the error saying the formual is wrong. Neal -- NealMed "OssieMac" wrote: 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 |
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 |
CF help needed with cell color
Hi again Neal,
I have just realized that I did not correct the double quote characters when I copied the guide into the post. I actually edited the guide from one I had in Word and it uses different characters for double quotes. (Angled instead of vertical.) Try this instead (I have only replaced the double quotes):- =FIND("MCR",A1,1)0 You say "Even if i copy the formula as written into a blank page". I assume you mean into formula box in the conditional format dialog box. As you said, you do need to change the A1 to suit the top left cell of your selection for the conditional formatting. My apologies for not properly editing the formula when I posted it. Regards, OssieMac "NealMed" wrote: Ossie, I thank you for your help. I had figured out the 1st one, that is sensitive to how it's written, the latter, Even if i copy the formula as written into a blank page, gives me an error. I'm not typing, just copy and pasting, to see what happens. I can't find any problem with what you wrote, it reads fine to me, can you help me trouble shoot it. I did try to change the A1, to C6 because that is the first active cell of use, but I still get the error saying the formual is wrong. Neal -- NealMed "OssieMac" wrote: 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 |
CF help needed with cell color
Ossie,
So far everything is great. Thanks for your help. I was at work, putting this plan together and found a problem, I thought you could help with. We we represent our referral sources by changing cell colors to match each one of them. The problem is that once the MCR is written in a cell changing the border the way i want it to appear, we can no longer change the cell color. Is there a way to make this function corretly?-- NealMed "OssieMac" wrote: Hi again Neal, I have just realized that I did not correct the double quote characters when I copied the guide into the post. I actually edited the guide from one I had in Word and it uses different characters for double quotes. (Angled instead of vertical.) Try this instead (I have only replaced the double quotes):- =FIND("MCR",A1,1)0 You say "Even if i copy the formula as written into a blank page". I assume you mean into formula box in the conditional format dialog box. As you said, you do need to change the A1 to suit the top left cell of your selection for the conditional formatting. My apologies for not properly editing the formula when I posted it. Regards, OssieMac "NealMed" wrote: Ossie, I thank you for your help. I had figured out the 1st one, that is sensitive to how it's written, the latter, Even if i copy the formula as written into a blank page, gives me an error. I'm not typing, just copy and pasting, to see what happens. I can't find any problem with what you wrote, it reads fine to me, can you help me trouble shoot it. I did try to change the A1, to C6 because that is the first active cell of use, but I still get the error saying the formual is wrong. Neal -- NealMed "OssieMac" wrote: 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 |
CF help needed with cell color
Hi Neal,
There is no way of having both that I am aware of. However, it might be worth while placing a new question on this forum because I have been wrong before and no doubt I will be in the future. To the best of my knowledge conditional format over rides any other format and therefore you have to delete the conditional format first. To delete conditional format from a cell:- Click on the cell. Select the menu item Format Select Conditional Formatting-Delete button Then check the box for the level to delete. (In your case it will be condition 1) If there is no other formatting in the cell, you can simply use Clear formats. However, it takes out all formatting including bold and number/date formats etc and returns it to the 'General' format state. To do this:- Click on the cell Select menu item Edit Select Clear- Format If the above is a viable option then I suggest that you place a button on your toolbar for 'Clear Format'. To do this:- Click on menu item View-Toolbars-Customize. Select Commands tab if it is not already displayed. Select Edit in the left column. Find 'Clear formatting' in the right column. Click on 'Clear formatting' and drag the button to any place you like on your toolbar. Regards, OssieMac |
| All times are GMT +1. The time now is 10:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com