Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Conditional Formatting

Answered
Request Demo

Is there a way to set up a conditional fomat when the adjacent cell contains a specific value? In my example below I want cell H2 to be highlighted when cell G2 contains an * or any other value that I choose.

0

Comments

3 comments

Date Votes
  • Mike

    Hi Scott!

    Great question! I enjoy a challenge for sure. I was able to get this to work with a little bit of elbow grease. Here's how I did it.

    In the cells H2:H, I created the following formula:

    =IFERROR(FIND("*",G2,1),"")

    This essentially looks for the asterisk value in the adjacent cell starting at position 1 and gives a value back of where it finds it. If it doesn't find, the formula would error out, which is why I added the IFERROR at the beginning, to be blank if nothing is there.

    Next, for the conditional formatting, I set the following:

    So it looks at the value in cells H2:H and if they are greater than 0, meaning it finds an asterisk somewhere, it highlights in my customer format. I chose to have my highlight and text color the same. If not, you would have numeric values in those cells, like so:

    Of course, its up to you there on how you want your conditional format to appear, but its an option. And as you mention, you can call out whatever item you are looking for by simply changing the asterisk value in your formula to whatever else you are looking for in column G. You could also create a reference point too and change only that value.

    Let me know if you have any questions for me on the above, or need anything else. Thanks much, and have a great day!

    0
  • Barbara Glenn

    so has there been any updates to conditional formatting.  I want the font to change color in cell B2 when A2 has a + or -, so the font in B2 would be green if A2 has + and red if A2 has -.  Then I want B3 to be based on A3, and so on

    0
  • Mike

    Hi Barbara!

    There's not been any new changes, per se, to conditional formatting, but I think i was able to get what you are looking for. For example, I used the following conditions for + (values >0) and -0 (values <0). I applied this to column B (B2:B).

    The result I tested came out as follows.

    Does that achieve what you are looking for, or have I missed the mark there? Let me know and I'll be happy to help. Thank as always for your contributions and have a great day!

     

    0

Please sign in to leave a comment.