Report

Cell colour containing specific info [Solved]

Ask a question JohannGC 3Posts Thursday June 22, 2017Registration date June 23, 2017 Last seen - Last answered on Jun 23, 2017 at 09:00 AM by JohannGC
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
Helpful
+0
plus moins
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 3Posts 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?
Reply
Leave a comment
Helpful
+0
plus moins
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 3Posts 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
Reply
Leave a comment
Helpful
+0
plus moins
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
JohannGC- Jun 23, 2017 at 09:00 AM
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.
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!