So is it now true in Excel 2007, that I have to apply conditional formatting
to each cell individually (in this case). In 2003, I was able to drag the
cell formatting down a long list of items where the formulas were relative so
the row number changed as I went from cell to cell. Excel 2007 seems to want
to lock everyting to a specific cell e.g., $m$4. If this is the case, it is
terrible. Perhaps I have missed something. I certainly hope so.

"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?

You can still apply the formatting to the entire range at once.

Let's assume you want to format A1:A10 if the cells contain Yes.

Select the *entire* range A1:A10 starting from cell A1. A1 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula used is relative to the active cell.

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=A1="Yes"
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Geo." wrote in message
...
So is it now true in Excel 2007, that I have to apply conditional
formatting
to each cell individually (in this case). In 2003, I was able to drag the
cell formatting down a long list of items where the formulas were relative
so
the row number changed as I went from cell to cell. Excel 2007 seems to
want
to lock everyting to a specific cell e.g., $m$4. If this is the case, it
is
terrible. Perhaps I have missed something. I certainly hope so.

"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II,
III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used
to
know how to do this in the old excel, but now I am using vista and
excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell
fill
color
when an "if" statement returns a "ture" response. How do I do this?

This method works, but I cannot get it to meet my specific needs.

By using your method, I was able to use a cell reference so that if =A1="no"
then Fill Cell RED and Apply to $B$1. This makes B1 red when A1 is "no". I
would like to apply this conditional format to an entire row B1-G1, however
it does not format all those cells, it only formats B1 even though the
conditional format shows it is applied to $B$1:$G$1. However, I CAN apply
this conditional format to colums, so that is a big help when I am dealing
with a table (make B2 red when A2="no", make B3 red when A3 . . . . and so
on). However, I can only apply these conditions to COLUMNS and for some
reason the rows won't take. Can you help me out? i keep having to apply the
same logical function to each column and telling that colum to reference
column A.

After writing this question I figured out my own answer and am now asking
another question. I realized that if my formula is =$A$1="no", then I CAN
apply the formatting easily to each row, however I cannot apply to columns
because then all rows will reference row 1 (if A1="no" then B2 is RED", but I
want them to reference in their own Row (if A2="no", B2 is RED). If I want to
apply easily to columns, my formula must be =A1="no". I realize that the
reason it is not keeping the formatting throughout rows is because when I
move over from B1 to C1, that cell is conditionally formatted based on B1,
and not A:1 which equals "no". C1 sees that B1 is not equal to "no" and
therefore doesn't turn to RED. So my new question is how do I make it so
that when I reference cell A1 in a conditional format, that the format
applies to cells in the same row (row x references cell Ax) and same column
(column x,y,&z all reference column A). In other words, I want the
referenced cell to be able to move up and down, but not side to side - how
can I get the cells around it to recognize so they can format (in rows)
according to that column? Like I said, I already have a method of doing
this, i'm just looking for a faster, easier way.

"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?

Sean, THANK YOU SO MUCH! I have the past 4 hours trying to figure out why my
conditional formatting wouldn't "paint" or "Copy Formats" to hundreds of
rows. After reading at at least 50 postings and even visited numerous Excel
users group, you are the first person to note the $B2 tip. Everyone else
said to eliminate the "$"...but that wasn't totally true...only before the
column and row. Again, thank 1,000,000

"Sean Timmons" wrote:

and just a throwaway.. if you want to evaluate column B of each row
individually, get rid of the $2 and instead use $B2. Otherwise, when you
copy the format down the rest of your rows, you'll still be evaluating B2...

"Gord Dibben" wrote:

FormatCFCondition1Formula is:

=$B$2-NOW()=30

Add Condition2 and 3

But you have a couple of conflicts with the <= and =

Who do you want to win if the value is 30


Gord Dibben MS Excel MVP

On Sun, 30 Aug 2009 20:52:01 -0700, Josh
wrote:

I am trying to shade the row if a particular cell in the row has a date value
in one of three categories. What I have done is not working. Statements are
below.

="If(($B$2-NOW)=30)"
="if(($B$2-NOW)<0)"
="If(($B$2-NOW)<=30)"

It is not coloring a single cell.Any ideas of better logic to use?

Hi

There is an easy way.
First use the MAX function to determine what maximal value you have in the
set of data.
Then enter conditional formatting and as the criteria for egual to you can
use the cell reference where you have the MAX function.
As a general rule you should always use cell references in conditional
formatting and never enter values there.

Hugo Jorgensen

"Brittany N Stinson" wrote:

Hi I would like to have cells change based on if it is the maximum of a range
of cells. For example 0 1 1 3 2 2
I would like the cell containing 3 to turn red because it is the maximum. I
have looked into the conditional formatting but dont see anything that could
help?

--
Brittany N. Stinson


"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?

THANK YOU SO VERY MUCH T. VALKO!! This was EXACTLY what I was looking for in
my own situation... it is so wonderful that folks like you share your
knowledge to folks like me!! THANK YOU!

"T. Valko" wrote:

Try this:

=AND(A116,A1<=19)

Replace A1 with the actual cell reference

--
Biff
Microsoft Excel MVP


"Ed R" <Ed wrote in message
...
Is there a way to do this if you are looking for a range of values?
Say
- Turn Green if <=16
- Turn Red if cell 16 and <= 19
- Turn Pruple of cell 19.

I can't seem to get the middle rule to work. It will run the cell Green
but
I can't get the compound rule to cancel out the Green and make it red


"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II,
III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used
to
know how to do this in the old excel, but now I am using vista and
excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell
fill
color
when an "if" statement returns a "ture" response. How do I do this?

I am working in Microsoft 2007. I have a situation where I would like to use
the "red light, yellow light and green light" icons in the conditional
formating. However, I would like the icon to be placed into it's own cell.
My spreadsheet is generating a value and putting it into cell B1, lets say
the value is 1.4. In cell C1, I have manually put a value of 1.0. I would
like to use conditional formating so that one of the three icons shows up on
cell D1 based on the value of B1 in comparison to the value of C1.
Specifically if B1 is = 1.0 then green, if B1 is between .99 and .90 then
yellow and below .9 then red.
Can you help? Thanks,

"Tree" wrote:

THANK YOU SO VERY MUCH T. VALKO!! This was EXACTLY what I was looking for in
my own situation... it is so wonderful that folks like you share your
knowledge to folks like me!! THANK YOU!

"T. Valko" wrote:

Try this:

=AND(A116,A1<=19)

Replace A1 with the actual cell reference

--
Biff
Microsoft Excel MVP


"Ed R" <Ed wrote in message
...
Is there a way to do this if you are looking for a range of values?
Say
- Turn Green if <=16
- Turn Red if cell 16 and <= 19
- Turn Pruple of cell 19.

I can't seem to get the middle rule to work. It will run the cell Green
but
I can't get the compound rule to cancel out the Green and make it red


"T. Valko" wrote:

I am using vista and excel 2007.

Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II,
III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used
to
know how to do this in the old excel, but now I am using vista and
excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell
fill
color
when an "if" statement returns a "ture" response. How do I do this?