Conditional format based on another cell containing text

Closed
stakik - Mar 5, 2015 at 03:21 AM
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



Related:

1 reply

cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 5, 2015 at 09:10 PM
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
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 5, 2015 at 09:10 PM
0
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

Jan 8, 2017 at 06:49 PM
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
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 7, 2015 at 11:00 AM
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