Conditional format based on another cell containing text [Closed]

- - Latest reply: Corrina66
Posts
1
Registration date
Sunday January 8, 2017
Status
Member
Last seen
January 8, 2017
- Jan 8, 2017 at 06:49 PM
Hello,
i want to format a cell(make it red and negative value) if the cell next to it cantains one of the words: cancelled_by_guest.
thanks
stavros



See more 

1 reply

Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
0
Thank you
Hello Stavros,

Here's a possibility for you. The spreadsheet at the DropBox link below will format the cell value as red and will show another cell with a negative number. I'm thinking this does most of what your want but doesn't overwrite the value of the original booking.

It's a matter of
1. Using conditional formatting based on the contents of the adjacent cell and
2. Using an "If" statement to multiply the cell contents by -1 and entering that result into another cell based on the same cell's content.

Hope that give you an idea where to go with it...
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7 -
Corrina66
Posts
1
Registration date
Sunday January 8, 2017
Status
Member
Last seen
January 8, 2017
> cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
-
Hi,

I can't seem to access the dropbox. Are you able to resend or copy the formula here?
Many thanks,
Corrina
Thank you a lot for your answer , you are right , not only you gave me an idea ,you show me the way to do what I want. the only problem is that mo excel 2007 I am using every time I try this (if) formula responds that the fomula is wrong.
i will fix it now that you show me the way .it must be the ''.
thanks again
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7 -
With Conditional formatting you want to be careful with the absolute cell references. If you see $B$1:$B$7 you must know it means only those cells. I've found that typing B1:B7 sometimes helps to copy correctly from one cell to another.

Enjoy.