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.
Bookmarks