Change background color using conditional formatting

[Guest]

I have a question about conditional formatting. I have cells beginning with
the text "no exceptions noted", and others beginning with the text
"exceptions noted". For "no exceptions noted" I would like conditional
formatting to change the cell to a red background color and, for the other, a
green background color. I tried using conditional formating with cell value
equal to each of the texts (and changing the result to the appropriate
background color) but it didn't work.
Any suggestions?

Thanks!

 

[Gord Dibben]

You have to watch for Excel being helpful at times.

Select your cells and Format>CF>Cell value is:equal to no exceptions noted

Do not add any quotes or Excel will double them up.

Add condition 2 which is:equal to exceptions noted

Ok your way out. If no joy, go back in and see if Excel has added too many
quotes.

The dialog box should contain only ="no exceptions noted"


Gord Dibben MS Excel MVP

 

[Pete_UK]

If you have any leading or trailing spaces in those cells, or other
text, then you will have to select Formula Is instead of Cell Content
Is and then enter these formulae as appropriate:

1st CF: =LEFT(TRIM(B2),19)="no exceptions noted" -->set patterns
to red
2nd CF: =LEFT(TRIM(B2),16)="exceptions noted" -->set patterns to
green

Ensure that Excel does not add extra quote marks to the formulae.

Hope this helps.

Pete

 

[David Biddulph]

As ever, the statement "it didn't work" is not a desparately useful input to
our crystal balls. The best suggestion I can make is that you describe in
more detail exactly what symptoms you are getting.

Copy from the Conditional Formatting dialogue to the newsgroup the formulae
that you are using. [Don't retype them; just copy and paste.]

At that stage you may, of course, see that the formulae aren't what you
intended, but that Excel has inserted quote marks or absolute references
that you hadn't intended. This problem can usually be avoided by starting
your formula with the equals sign, and not letting Excel put that in for
you.

If you are happy with the formulae, tell us what values are in the relevant
cells, what answer you expected, and what answer you got.

One point in your description is that you said that you had cells BEGINNING
with the relevant text, but then you said that your CF had "cell value equal
to" the text. If you want a response for text BEGINNING with your specified
text, don't you need something like
Formula Is =LEFT(A1,19)="no exceptions noted" ?

 

[Guest]

Thanks to all for your suggestions. Because the cell in question begins with
the text I mentioned, I had to change the CF from Cell Value to Formula Is,
and use the LEFT function, in order to produce the desired result. Again,
thanks to all.

As ever, the statement "it didn't work" is not a desparately useful input to
our crystal balls. The best suggestion I can make is that you describe in
more detail exactly what symptoms you are getting.

Copy from the Conditional Formatting dialogue to the newsgroup the formulae
that you are using. [Don't retype them; just copy and paste.]

At that stage you may, of course, see that the formulae aren't what you
intended, but that Excel has inserted quote marks or absolute references
that you hadn't intended. This problem can usually be avoided by starting
your formula with the equals sign, and not letting Excel put that in for
you.

If you are happy with the formulae, tell us what values are in the relevant
cells, what answer you expected, and what answer you got.

One point in your description is that you said that you had cells BEGINNING
with the relevant text, but then you said that your CF had "cell value equal
to" the text. If you want a response for text BEGINNING with your specified
text, don't you need something like
Formula Is =LEFT(A1,19)="no exceptions noted" ?
--
David Biddulph

I have a question about conditional formatting. I have cells beginning with
the text "no exceptions noted", and others beginning with the text
"exceptions noted". For "no exceptions noted" I would like conditional
formatting to change the cell to a red background color and, for the
other, a
green background color. I tried using conditional formating with cell
value
equal to each of the texts (and changing the result to the appropriate
background color) but it didn't work.
Any suggestions?

Thanks!