Conditionally format a whole row 2

[Topcat04]

Hi There

I have recently moved to Excel 2007 and am having teething trouble with the new formulas and enhanced options.

I would like to turn the row A:V black background with red writing based on b containing the word "Delisted"

I see there is an option now with specific text, but I was unable to select the whole row and specify the column for the word to be in.

Hopefully a real simple one for you experts...I did check the FAQ but couldnt find what I was looking for.

Could you help and post the correct way to do this please.

Thanks for your help!

 

[SkipVought]

Hi,

Don't have 2007 in front of me now, but try to find an option where you can enter a formula. I have done it, but not recently and not often enough to remember the steps.

optionally, try...
[tt]
alt+o d

that's

alt+Format > Conditional Formatting...
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Select all of your data ([Ctrl] + [*])

Cell A1 should now be active....

Go to Home > Styles > Conditional Formatting > New Rule

Select Use a formula to determine which cells to format

In the Format values where this formula is true: box, type in
[tab][COLOR=blue white]=$B1 = "Delisted"[/color]

Click on Format and set it however you like


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Topcat04]

Hi John

Thanks for the help,
How do I make this so that this will still work, when the cell contains more than just delisted.

Eg "Product 1 - Delisted"

Thanks!

 

[SkipVought]

[tt]
=not(iserr(FIND("delisted",$b1)))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Sorry, I couldnt get that to format the cells...
I copied it directly from here and pasted it in

I must be missing something..

 

[SkipVought]

[tt]
=not(iserr(FIND("Delisted",$b1)))

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI,

FIND is case sensative. SEARCH is not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Excellent! thanks Skip that got it working.

Taking it one step further, is there a way that the cell with the word Delisted in it is slightly formatted differently to the rest of the row?

What I would ultimately like to achieve is black font on a black background, apart from the column B and the cell that has the word Delisted in it, then that has Red Font?

Thanks again!

 

[SkipVought]

Yes, just format column B separately.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

FYI: You can apply the first CF as we outlined before, then just apply an additional CF only to column B. This saves you from having to apply 3 different CFs to Col A, Col B and Cols C-??

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Topcat04]

Brilliant! Thanks!