Conditional format based on another cell containing text

Closed
Report
-
Posts
1
Registration date
Sunday January 8, 2017
Status
Member
Last seen
January 8, 2017
-
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



Related:

1 reply

Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
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...
0
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
0
Posts
1
Registration date
Sunday January 8, 2017
Status
Member
Last seen
January 8, 2017
>
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
0
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
0
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.
0