Cell colour containing specific info [Solved/Closed]

JohannGC 3 Posts Thursday June 22, 2017Registration date June 23, 2017 Last seen - Jun 22, 2017 at 03:48 AM - Latest reply:  JohannGC
- Jun 23, 2017 at 09:00 AM
Hi, I am currently using Microsoft Office Excel 2016. I am busy doing a spread sheet for work and need to know how to change a cell colour if a different cell contains the letter "Z". I have been using conditional formating but the progress is slow.

I want to change cell AA4 to green if B4 contains the letter "Z".

Any assistance would be appreciated.

Thanks
See more 

6 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Jun 22, 2017 at 07:45 AM
0
Thank you
JohannGC, Good morning.

Try to use:

Select AA4

Menu Conditional Format
Using formula

--> =B4="Z"
--> Format: Fill color as GREEN
OK

Please, tell us if it worked as expected.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
JohannGC 3 Posts Thursday June 22, 2017Registration date June 23, 2017 Last seen - Jun 22, 2017 at 08:09 AM
Hi Marcilio, Thank you for the response. The formula does work if I only use the letter "Z" but I need to insert a formula for the cell containing a "Z". The Z used is only to mark a new customer and a code gets allocated such as Z01, Z02, Z03 etc. So using only Z does not work for my application. Is there a formula I can use to set it as containing Z or starting with Z?
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Jun 22, 2017 at 10:36 AM
0
Thank you
JohannGC,

"... Is there a formula I can use to set it as containing Z or starting with Z?..."
Yes.

Starting with Z
=LEFT(B4,1)="Z"

Containing Z
=ISNUMBER(SEARCH("Z",B4))

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
JohannGC 3 Posts Thursday June 22, 2017Registration date June 23, 2017 Last seen - Jun 23, 2017 at 02:29 AM
Hi Marcilio,

Thank you for the reply. When I use those formulas I receive the error saying "there is a problem with this formula." and help states that Excel does not understand what I am trying to do.

I followed the following steps.

1. I select the AA4 cell (the cell I want to change the colour)
2. Conditional Formatting ----> New Rule ----> Use a formula to determine which cells to format.
3. Format values where this format is true ----> =LEFT(B4,1)="Z"

I am not sure why it does this.

Regards
Johann
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Jun 23, 2017 at 08:36 AM
0
Thank you
Johann, Good morning.

Your procedure is correct.

I'm guessing that your version of Excel is in English.

However, depending on the country, the parameter separator can be the comma "," or the semicolon ";".

Do your formulas use a comma or a semicolon?

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hi Marcilio,

I did figure out that it was a ";" and not a "," thank you very much. I used the formula starting from the left and it worked perfectly.

Keep well.