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 response

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