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?

1. Select the cell or range of cells where you want to apply the conditional formatting.
2. Go to the Home tab in the ribbon and click on Conditional Formatting in the Styles group.
3. Choose New Rule from the drop-down menu.
4. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
5. In the Format values where this formula is true field, enter your if logical function. For example, if you want to highlight cell A1 if it contains the value "Yes", you can enter the formula
   Formula:

   =A1="Yes" 

   (without the quotes).
6. Click on the Format button to choose the cell fill color you want to apply.
7. Click OK to close the Format Cells dialog box.
8. Click OK again to apply the conditional formatting rule.

Now, whenever the if statement in your formula returns a true response, the corresponding cell will be highlighted with the cell fill color you chose. You can also customize the formatting options further by adding additional rules or modifying the existing ones.

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?

Select the cell(s); use Format | Conditional Formatting
In the dialog box you need: Cell Value is Equal to TRUE
The click the Format button in the dialog and open the Pattern tab
Select the fill colour you want
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"George_Sky" wrote in message
...
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 guess that my question is unclear. I do not want to change the content of
the cell, just the highlighting of the cell (i.e. If x=A, "B" with cell
highlighted,"B" with cell not highlighted)

"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 guess my question was not clear. I want to change the highliting of the
sell, not the cell content. (e.g. IF X=A, "B" with cell highlighted, "B"
winhout cell highlighted.)

"Bernard Liengme" wrote:

Select the cell(s); use Format | Conditional Formatting
In the dialog box you need: Cell Value is Equal to TRUE
The click the Format button in the dialog and open the Pattern tab
Select the fill colour you want
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"George_Sky" wrote in message
...
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?

George_Sky wrote...
I guess my question was not clear. I want to change the highliting of the
sell, not the cell content. (e.g. IF X=A, "B" with cell highlighted, "B"
winhout cell highlighted.)

Your original question *and* the two responses so far were all clear.
What's unclear is your ability or willingness to understand the
responses.

"Bernard Liengme" wrote:
Select the cell(s); use Format | Conditional Formatting
In the dialog box you need: Cell Value is Equal to TRUE
The click the Format button in the dialog and open the Pattern tab
Select the fill colour you want
best wishes

Conditional formatting DOES NOT affect cell values/contents.

Conditional formatting CAN affect the FORMATTING of cells based on the
values either in the cell in question or other cells.

Looks like you want to use Formula Is rather than Cell Value Is
conditions. Select the cell containing "B", run the menu command Format
Conditional Formatting..., use the drop-down list on the left side of the Condition 1 box to change to Formula Is, in the entry field to the right of it enter the formula =X=A, then click on the Format... button and select the particular form of highlighting you want, then click OK in the Format Cells dialog then click OK in the Conditional Formatting dialog. The cell's value/contents will remain as-is but its format will change as the value of X=A changes.

To be honest, I found both the question and the response to be very confusing.

"Harlan Grove" wrote:

George_Sky wrote...
I guess my question was not clear. I want to change the highliting of the
sell, not the cell content. (e.g. IF X=A, "B" with cell highlighted, "B"
winhout cell highlighted.)

Your original question *and* the two responses so far were all clear.
What's unclear is your ability or willingness to understand the
responses.

"Bernard Liengme" wrote:
Select the cell(s); use Format | Conditional Formatting
In the dialog box you need: Cell Value is Equal to TRUE
The click the Format button in the dialog and open the Pattern tab
Select the fill colour you want
best wishes

Conditional formatting DOES NOT affect cell values/contents.

Conditional formatting CAN affect the FORMATTING of cells based on the
values either in the cell in question or other cells.

Looks like you want to use Formula Is rather than Cell Value Is
conditions. Select the cell containing "B", run the menu command Format
Conditional Formatting..., use the drop-down list on the left side of the Condition 1 box to change to Formula Is, in the entry field to the right of it enter the formula =X=A, then click on the Format... button and select the particular form of highlighting you want, then click OK in the Format Cells dialog then click OK in the Conditional Formatting dialog. The cell's value/contents will remain as-is but its format will change as the value of X=A changes.

On Jun 20, 6:11 pm, John wrote:
To be honest, I found both the question and the response to be very confusing.


Well, you dredged up a 2 year old thread for an offhand comment.
That's a little confusing, also.

But I'll assume you want to know about this. Maybe you should go open
Conditional Formatting to see what they were talking about. It won't
make sense if you don't look at it. It's in the Format menu in Excel.
Miguel's original response was right on, although Bernard's was more
detailed.

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 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?

Thanks for your help. Worked out perfect!!

"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're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
Thanks for your help. Worked out perfect!!

"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?

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '<=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help

Thanks Gord,

the add-in is helpful.
If i sent this same spreadsheet or opened it at another computer without the
add-in.
will the conditions still work? or on a different excel version.

Also,
i tried the event code: it's didn't work.
currently in using in excel 2003.

"Gord Dibben" wrote:

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '<=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help

The recipient(s) would require CFPlus add-in to be accessible.

What is "didn't work" with regards to the event code?

The code needs to be pasted into the sheet module.

Right-click on the sheet tab and "View Code".

Paste the code to that module.


Gord

On Tue, 7 Oct 2008 12:15:01 -0700, MT wrote:

Thanks Gord,

the add-in is helpful.
If i sent this same spreadsheet or opened it at another computer without the
add-in.
will the conditions still work? or on a different excel version.

Also,
i tried the event code: it's didn't work.
currently in using in excel 2003.

"Gord Dibben" wrote:

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '<=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help

Hello
Do you know how to do this in 2007 excel?

Thanks!

"Spiky" wrote:

On Jun 20, 6:11 pm, John wrote:
To be honest, I found both the question and the response to be very confusing.


Well, you dredged up a 2 year old thread for an offhand comment.
That's a little confusing, also.

But I'll assume you want to know about this. Maybe you should go open
Conditional Formatting to see what they were talking about. It won't
make sense if you don't look at it. It's in the Format menu in Excel.
Miguel's original response was right on, although Bernard's was more
detailed.

"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?

Thanks for this tip. It worked for me too.

"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?

Glad to help. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Peace153" wrote in message
...
Thanks for this tip. It worked for me too.

"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?

How can I incorporate a format into a if statement?
For example
Sum if Cells fill color is red?

You'll need VBA
http://www.cpearson.com/excel/colors.aspx
http://xldynamic.com/source/xld.ColourCounter.html
--
David Biddulph

"Jim C" <Jim wrote in message
...
How can I incorporate a format into a if statement?
For example
Sum if Cells fill color is red?

Try Conditional Formatting from the Format tool bar.

"Jim C" <Jim wrote in message
...
How can I incorporate a format into a if statement?
For example
Sum if Cells fill color is red?

Corey

Info only.................you cannot CF on a fill color without using VBA


Gord Dibben MS Excel MVP

On Thu, 22 Jan 2009 12:01:37 +1100, "Corey"
wrote:

Try Conditional Formatting from the Format tool bar.

"Jim C" <Jim wrote in message
...
How can I incorporate a format into a if statement?
For example
Sum if Cells fill color is red?

Hello there

Wondering if you can help.

I have problem with worksheet at work. Running 2003. I have in some cells
formula with if and hlookup. It picks up colour in cell pending answer. IE
if comes back due then cell says due and changes to yellow if expired then
expired returns andcell changes to red. THese have been set up for some time.
I have had to add new columns and rows. In the new ones they are changing
to due and expired but not picking up colours. I have looked into
conditional formatting but it is blocked and I cannot select it.

Can you advise something please.

Also if can help. I can't merge cells on the new ones I have added. I have
checked and the worksheet is not locked.

Thanks

Lee

"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?

If CF is not available and worksheet is not protected perhaps the workbook
has been shared under ToolsShare Workbook.

Unshare to regain CF capability.


Gord Dibben MS Excel MVP


On Tue, 5 May 2009 23:21:01 -0700, Lee
wrote:

Hello there

Wondering if you can help.

I have problem with worksheet at work. Running 2003. I have in some cells
formula with if and hlookup. It picks up colour in cell pending answer. IE
if comes back due then cell says due and changes to yellow if expired then
expired returns andcell changes to red. THese have been set up for some time.
I have had to add new columns and rows. In the new ones they are changing
to due and expired but not picking up colours. I have looked into
conditional formatting but it is blocked and I cannot select it.

Can you advise something please.

Also if can help. I can't merge cells on the new ones I have added. I have
checked and the worksheet is not locked.

Thanks

Lee

"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?

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?

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?

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?

is there any way to write an IF statement that does the following:

=IF(A1=pink,"Yes","No")



"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?

Hi,

I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.

it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red)

You need to write a formula that returns either TRUE or FALSE, so something like this would work:

=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1"
and set the fill to red...

You, of course, would need either two or three conditions - if you have only three conditions, the
default formatting can take care of one condition, and the CF can take care of the other two.

HTH,
Bernie
MS Excel MVP


"tyronki" wrote in message
...
Hi,

I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.

it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red)

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?

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?

You can let the internal engine do it.

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?

I think the quotation marks around your IF statement are probably
messing you up.

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

You will still have the logic issues that Gord pointed out.

Ken

On Aug 30, 11:52*pm, 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?

Ken

Don't need to use IF

Also NOW is a function and syntax is NOW()


Gord

On Mon, 31 Aug 2009 14:11:52 -0700 (PDT), Ken wrote:

I think the quotation marks around your IF statement are probably
messing you up.

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

You will still have the logic issues that Gord pointed out.

Ken

On Aug 30, 11:52*pm, 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, thanks a million Bernie.

My conditional formatting applies to a number of columns too- different
dates.. Your formula applies the format to the whole row. So what if I only
wanted to apply the formatting to the cell in that row which wasn't empty.
(it will contain the same job number which was used as the lookup value- i.e.
A5)
I'm running into a similar problem where the cells I want to reference in
the conditional formatting formula are the cells that the format actually
applies to.
e.g: I wish I could do this in my formula:

=AND(VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1", NOT(ISBLANK("cells in row
9 to which formula applies")))


my spreadsheet looks much like ms project with jobs on the left, dates along
the top and coloured cells where the dates and jobs correspond.

Cheers for the help,
Tyron

"Bernie Deitrick" wrote:

You need to write a formula that returns either TRUE or FALSE, so something like this would work:

=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1"
and set the fill to red...

You, of course, would need either two or three conditions - if you have only three conditions, the
default formatting can take care of one condition, and the CF can take care of the other two.

HTH,
Bernie
MS Excel MVP


"tyronki" wrote in message
...
Hi,

I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.

it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red)

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?