I have searched far and wide but cannot seem to find a question that answers this.
I am trying to highlight/color an entire row in Microsoft Excel for Mac (16.33) based on two different values in two different columns.
This is my current forumla:
=AND(SEARCH("no",INDIRECT("C"&ROW())),SEARCH("yes",INDIRECT("D"&ROW())))
From my understanding, it is searching first in column C for "No", then proceeding to search column D for "yes". Enclosing this in AND() must ensure that both cases are true. If they are not, it returns false and does not apply the formatting.
This formula should return something like this, which works perfectly with the above example in Google Sheets:
Column D contains a yes, but Column C also contains a no, rendering the entire row yellow.
Since conditional formatting works top to bottom, it moves on to the next rule which is:
=SEARCH("yes",INDIRECT("D"&ROW()))
Hence, two "yes" return full green across the row.
I cannot get this to work in Excel with the same exact formulas. I have ensured the correct cell range has been selected for conditional formatting.
