+ Reply to Thread
Results 1 to 11 of 11

Show MATCH when all cells in a row have been conditionally formatted regardless of color

  1. #1
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Show MATCH when all cells in a row have been conditionally formatted regardless of color

    Show MATCH when all cells in a row (IGNORING BLANK ONES) have been conditionally formatted regardless of color.


    I have six ranges of data, each is 11 columns by 70 rows.

    I have Conditional Formatting in each cluster to the effect that if on another cell (N2) I type a value and that value is found within all the six clusters of data then all the cells containing that value will be highlighted (to a specific color that I already set).

    Up to that point all is fine.

    -----------------------------

    Now, I need to display MATCH in a cell at the bottom of the cluster when ALL cells in a row (ignoring all the blank ones and only in one cluster of data) have been conditionally formatted (regardless of color) and change the font color of all the cells that meet the criteria.

    I know it sounds confusing... I'm dizzy myself... but to illustrate the scenario, I attached a file with dummy data.

    In the attached file, to illustrate my point, I manually changed the FONT COLOR to all the cells that meet the criteria (all the cells in that row have been conditionally formatted with different colors, obviously), and I also manually added at the bottom of each cluster of data a MATCH FOUND cell since that's what I also need to see as I manually input specific numbers (in another area of the sheet, N2 through N7) that I'm looking for.

    I tried with the following formulas but to no avail (the VB module is also in place)...

    =REPT("MATCH",COUNT(1/MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,N(P$2:Z$71=O2)))=11) array formula

    =IF(SUM(--(MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,--(O2=P$2:Z$71))>0))=11,"MATCH","") regular formula


    In the examples I have found in the forums, they count how many cells have been conditionally formatted... this is slightly different.


    Thank you for helping.
    Last edited by Luisftv; 11-23-2018 at 10:57 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    Not sure if I get this right.

    Please try at P74 with CSE

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I edit B7 to 5+10+11=

    This will result 2 as all number in P7:Z7 and P16:Z16 match number from N2:N6

  3. #3
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    you are absolutely right, I missed that one 5+10+11...

    However, if I remove the value in cell N15 (which is the number 15), now all the MATCH cells turn to zero... when in fact there should be several clusters of data MATCH still showing 1 (one)...

    The same happens if I remove any of the values in the other manually input cells... N2 through N7.

    And... how do I apply conditional formatting to the found matches (that are already conditionally formatted)? I mean, to change the font color or to put a ,say, red border around all the the rows that have all the cells conditionally formatted?

    Do I make sense? Sorry if I don't...
    Last edited by Luisftv; 11-24-2018 at 01:13 AM.

  4. #4
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    I found why some of them were not changing... in the formula, it was stooping at row 6 when it should stop at row 7...

    But still... if I remove all the manual values from the range N2:N7 except for one... say the value 10 in N2... the formula at the bottom of the cluster will not show 1 (one) since there is one cluster tat contains one row with only one cell with conditional format since there is only one value, 10.

  5. #5
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    Here is another problem:

    After adjusting the array formula to reflect the full range for the manual input, from N2:N6 to N2:N7 then the cell that will tell how many are found to MATCH will not count all the clusters with matching criteria (where a row has all cells conditionally formatted)... when in fact there are several that MATCH.

    For example, I entered the number 2 and the number 10 (the rest are empty in the N2:N7 range)... then there are 2 clusters of data with at least one row full of conditional formatting... but the array formula will not count them. The array formula will ONLY COUNT them when all six manual input cells (N2:N7) have a value.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    I adjust formula to N2:N7

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Formula for CF apply to P2:Z71

    =SUM(COUNTIF($P2:$Z2,$N$2:$N$7))=1/(1/COUNT($P2:$Z2))

  7. #7
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    Bo_Ry

    We are almost there...

    Please see attached file.

    Clusters 1, 4, and 6 work just fine... they do a correct count.

    BUT Clusters 2, 3, 5 DO-NOT-DO an accurate count... something is wrong there... at the bottom of the cluster...

    If I remove values from the range N2:N7, clusters 1,4, and 6 will show the correct value but the others will not.

    NOTE: The conditional formatting that highlights all "conditionally formatted" cells in a row is good... no problem there. It's only the count at the bottom of each cluster that has a glitch.
    Last edited by Luisftv; 11-24-2018 at 03:44 PM.

  8. #8
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    I narrowed the problem...

    None of them will count the first row... regardless if the row has a total of one conditionally formatted cell or more, it simply will not count that first row. In other words, it is skipping altogether the first row.

  9. #9
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    Bo_Ry

    It's done... I figured out where I was messing up.

    I could have not done it without you!!!

    Thank you so much.

    I attached here the final Excel file that works perfectly.

    My mistake was that the formula that counts how many rows are full of Conditional Formatting was starting at row 2 when it should start at row 1... although that row is not being used at all, except to label the cluster of data.

    Also, I noticed that in some of the formulas the ROW was missing so I added it and voila! You had it on the formula for the first cluster of data, but not for the others.

    Thank you thank you thank you!!!!

    Have a great weekend.
    Last edited by Luisftv; 11-24-2018 at 05:06 PM.

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    Luisftv,

    That's great, and thanks for the rep.

    If this takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    Registered User
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    78

    Re: Show MATCH when all cells in a row have been conditionally formatted regardless of col

    I did... I checked on SOLVED at the same time that I submitted your rep.

    Thank again. Have a great day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] if two cells match show value and then conditionally format.
    By M12NIX in forum Excel General
    Replies: 2
    Last Post: 02-24-2017, 02:04 AM
  2. [SOLVED] if two cells match show value and then conditionally format.
    By M12NIX in forum Excel General
    Replies: 2
    Last Post: 02-01-2017, 12:25 PM
  3. Replies: 8
    Last Post: 05-21-2016, 08:08 PM
  4. need a macro to convert conditionally formatted cells color to excel colors.
    By efi007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2015, 06:39 AM
  5. Replies: 6
    Last Post: 02-16-2013, 05:12 AM
  6. Replies: 2
    Last Post: 09-22-2010, 07:54 PM
  7. Extracting Font Color from a Conditionally Formatted Cell
    By rsromano in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-27-2010, 04:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1