Forum Discussion
Conditional Formatting of Cells based on variable input in other cells.
ScottMN Perhaps something like in the attached file?
Riny_van_Eekelen- smashed it! That is awesome. I wish I understood the logic in the back a bit more to make it more intuitive (and know that some of these other cool functions exist) - but Ill take it.
Appreciate the prompt response mate - really helps me out.
ScottMN Conditional Formatting (CF), unfortunately, isn't always intuitive. Think of it this way. The range you want to format is B14:O23. That will thus be in the the "Applied to" field with dollar signs to make the references absolute. Now, the rule that checks if the numbers in the top part of the sheet exist in the bottom part uses MATCH. If the number exists it produces a number (representing the position where the number was found) or #N/A.
Then ISNUMBER(.......) will return ether TRUE or FALSE. Put it all in one CF rule like this:
=ISNUMBER(MATCH(B14,$B3:$O3,0))
Applied to the range mentioned above will lead to all cells with TRUE to be formatted.
The attached file includes a few extra rows to demonstrate just that with the MATCH part of the above formula entered in B25, copied across and down and the ISNUMBER formula in the rows below that.
Hope you'll find it useful.