Cell colour containing specific info [Solved/Closed]

Posts
3
Registration date
Thursday June 22, 2017
Status
Member
Last seen
June 23, 2017
-
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 

3 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
130
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
Posts
3
Registration date
Thursday June 22, 2017
Status
Member
Last seen
June 23, 2017
-
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?
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
130
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
Posts
3
Registration date
Thursday June 22, 2017
Status
Member
Last seen
June 23, 2017
-
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
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
130
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.