none
Conditional Formatting RRS feed

  • Question

  • Hello! 

    I am hoping that someone will be able to help me with some specific conditional formatting needs. Please use the attached screenshot for reference.

    I cell J6, I will by typing a date. In cells C8, D8, E8, F8, G8, and H8, I will be typing individual numbers (none of these numbers will equal one another). Additionally, in cells J8, K8, L8, M8, N8, and O8, I will be, again, typing individual numbers (none of which will equal one another). However, the numbers in cells J8, K8, L8, M8, N8, and O8 should equal the numbers typed in C8, D8, E8, F8, G8, and H8 (they may or may not be typed in the same order). Also, not all cells within these two sets (C:H and J:O) will necessarily contain values (some may be left blank).

    If the numbers in cells J8, K8, L8, M8, N8, and O8 are equal to the numbers typed in C8, D8, E8, F8, G8, and H8, I would like cell J6 (the date) to be highlighted green, and the text to turn green. If the numbers in cells J8, K8, L8, M8, N8, and O8 are not equal to the numbers typed in C8, D8, E8, F8, G8, and H8, I would like cell J6 (the date) to be highlighted red, and the text to turn red. 

    Thank you!

    Thursday, May 30, 2019 6:51 PM

All replies

  • Sorry all. It looks like I am unable to attach the image. I hope my description was enough.
    Thursday, May 30, 2019 6:58 PM
  • Hi,

    Maybe this is just my brain struggling but would you post an example of how this would look for green and red?  Pictures are always good :-)

    Am I right in thinking though that C8:H8 numbers, when you say they wont equal on another, that means they will be unique values?  And the same with J8:O8? Then if ANY  of the values in C8:H8 match ANY of the values in J8:O8 - that is the condition met for the date to be green?

    I think thats what you mean but a screenshot of a positive and negative result helps my brain work.

    Thanks,

    Mat

    Thursday, May 30, 2019 6:59 PM
  • Hi Mat,

    Thank you for getting back to me! I appreciate it. Unfortunately, I am unable to post a photo for some reason.

    The numbers in cells C8:H8 will be unique values when compared to one another and the numbers in cells J8:O8 will also be unique values when compared to one another, but the numbers in cells J8:O8 should be the same values as those typed in cells C8:H8 (but not necessarily in the same order).

    If ALL of the values in J8:O8 match those in C8:H8, I would like conditional formatting to automatically highlight the "date" cell (cell J6) green, and apply a green font color. If at least one of the values in J8:O8 is not included, or does not match, those in C8:H8, I would like conditional formatting to automatically highlight the "date" cell (cell J6) red, and apply a red font color. 

    Does that help? Thank you, again, for your assistance!

    Thursday, May 30, 2019 7:05 PM
  • If so, you will need an array function to group the column values together.  As that can be quite long to explain, check out this site and see if that is what you need
    Thursday, May 30, 2019 7:05 PM
  • Ah OK. Matching ALL of the values makes this more complicated.  I'm about to head out now but can come back to this thread tomorrow if it hasnt been answered by then.  Off the top of my head I cant say how to do this but I'll go through some logic tomorrow and see if I can figure it out.

    Definitely a good challenge here :-) 

    I would guess at sorting them in order in some way and then doing the comparison would be the easier way to go rather than cross checking them all


    Thursday, May 30, 2019 7:07 PM
  • Thank you so much! I really appreciate your help. I will stay tuned until tomorrow. Have a great evening!
    Thursday, May 30, 2019 7:10 PM
  • Please try this and see if that works for you.

    Select J6 and make two new conditional formatting rules using the following formulas...

    Green Rule:

    =ISNUMBER(SUMPRODUCT(MATCH(IF(C8:H8<>"",C8:H8),IF(J8:O8<>"",J8:O8),0)))

    Red Rule:

    =NOT(ISNUMBER(SUMPRODUCT(MATCH(IF(C8:H8<>"",C8:H8),IF(J8:O8<>"",J8:O8),0))))


    Subodh Tiwari (Neeraj) sktneer

    Friday, May 31, 2019 7:33 AM