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

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

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

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

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

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

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

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

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

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

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

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

=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

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

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

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