In Excel, conditional format (font red) referencing another cell?

[Kyle]

do you remember where you found your answer? or can you explain your answer
to us? we are using Excel 2007.

thank you

-Kyle

 

[CDQ]

HI

I have a similar question to Christine's conditional formatting.

I want to to format all the cells with blue in Column D if Column C has "NA".

Think there shouldnt be an easier way than setting hundreds of lines with =
C1="NA" in every cell in column D.

Many thanks!

 

[T. Valko]

You can apply the formatting to a range of cells all at one time.

Assume the range you want to format is D1100

In Excel 2007

Select the range of cells, D1100
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=C1="NA"
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Select the range of cells, D1100
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=C1="NA"
Click the Format button
Select the desired style(s)
OK out

 

[CDQ]

Bilff

It works! thanks!

You can apply the formatting to a range of cells all at one time.

Assume the range you want to format is D1100

In Excel 2007

Select the range of cells, D1100
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=C1="NA"
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Select the range of cells, D1100
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=C1="NA"
Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Becky]

I would like to format A1 with "strikeout" if A3 is 0 (zero). How do I write
that one?

 

[T. Valko]

You have replies at your other post

 

[Brian]

Ok, I was reading and I have the same issue for Excel 2007, I want to
conditionally format a blank cell, based on the % value of another cell. How
do I do that??

Thanks
(e-mail address removed)

 

[Gord Dibben]

Select the blank cell..........say A1

Home tab>Styles>Conditional Formatting>Manage Rules

New Rule>Use a Formula to determine............

=B1=50% Format to a fill color and OK


Gord

 

[Marque]

Thank you for the answer, this has perplexed me for years! Your awesome!

 

[Rick Rothstein]

I'm guessing that whoever you directed your message to will probably not
realize it was for them as you posted your thank you message in a brand new
thread rather than responding to the person you wanted to thank back in the
thread where he/she actually helped you at.

 

[Gail]

I have no idea how to highlight a whole row when I have a set condition.

You see, I need to shade a row of data (8-rows of content), yellow when it
is pending, and I need to turn those rows yellow/italic when the they are NOT
approved, and I have to get those same rows to turn black and Bold when they
ARE approved. How on earth can I get the conditional formating to do that,
and can you dumb it down completely, as I have no clue how to build out
formulas at all.

My data is for what program content has rights -- so I have a wide list of
shows, producers, distributors, etc. All the legal reps will do is indicate
"Y" "N" or "P" in the cell, and then carry on down to the next cell. There
could be hundreds of rows of content... is this even possible???

help please -- I can be reached at (e-mail address removed) or at (e-mail address removed)

 

[Bernard Liengme]

I have data on rows 1 thru 10,
In column C the data is either "pending" or something else
I clicked the mouse on the 1 for the row header for row 1 and dragged down
to row 10; the first 10 rows are selected

I used Format | Conditional Format in Excel 2003
In Excel 2007 open the Home tab and locate the Styles group and click on
Conditional Formatting | New Rule
Is used Formula Is: =$C1="pending" and clicked the Format button to give
that row a yellow pattern and italic font
The I added another Formula Is: =$C1<>"pending" and clicked the Format
button and make the pattern blue with bod font
A test shows this method worked as expected

See also Debra Dalgleish At:
http://www.contextures.com/xlCondFormat01.html
best wishes

 

[Brendan]

Hi Guys,

I am trying to set up a time reporting worksheet to incorporate conditional
formatting across a row based on the text value in the first column.

i.e.: cells A11 are filled in orange when the formula (=A1="Coffee") is true

I have had no problem with that portion of the process, but I can't figure
out how to apply it to more than one row at a time, much less the entire
worksheet. The only thing I have been able to do is get all of the selected
cells to turn orange when the specific cell A1="Coffee." Is there a way to
apply the conditional formatting rule of one set of cells to others?

Is there a way to incorporate conditional formatting formulas into a
standard "IF" formula?

Any help would be greatly appreciated

 

[T. Valko]

What version of Excel are you using and what is the *entire* range of cells
you want this to apply to?

 

[Brendan]

I am using 2007, and the range is 5 sheets of about 100 rows by 5 columns. I
need the conditional formatting of each A cell to apply only to the 4 cells
to the right. I can do it by copying the formatting row by row, but I don't
5 hours to devote to that. Any suggestions?

 

[T. Valko]

You can apply the formatting all at once (on one sheet at a time).

Let's assume you want to format the range A1:E100. When the cell in column A
= Coffee you want that row (A:E) to be highlighted.

First, delete any conditional formatting that you now have set for that
range. You want to start from scratch. Excel 2007 can really screw things up
if you try to "tweak" what's already there.

Select the entire range of cells A1:E100 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 will be relative to the active cell.
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=$A1="coffee"
Click the Format button
Select the desired style(s)
OK out

Repeat this process for the other sheets.

 

[cerkit]

I am also looking for a way to do this -- use a 2-color gradient fill *based
on the value of a different cell*.

Did you ever find a solution (or does anyone else have one)?

 

[Gord Dibben]

Select A1

CF>New Rule>Use a formula> =D1="mytext" or =D1>100

Format>Fill Effects>Gradient.................make your choice of color(s)
and gradient type.


Gord Dibben MS Excel MVP

 

[kpkammer]

Is there a way to apply this to an entire row or range?