Forum Discussion
Turn off condition formatting of a cell based on different cell
Good Morning, I have a formula running in cell K11 based on other cells (J11 and current date) that provides a number, then conditional formatting to highlight the cell based on the value.
Next I wish to use information from a 3rd cell (M11) to turn off the formula and formatting in K11, Once anything is present it M11, K11 goes blank with no highlight.
Likely this is a new formatting rule --> Use a formula to determine which cells to format, that's as for as I get. Can anyone help with this? Thanks....
I updating the formulas a bit starting from row 12 and till end of the range.
In I12
=IF($G12="","",$G12-($H12+$I12)*7)it shows empty cells in column J if no req date (col G)
In K12
=IF(OR($M12<>"",$G12=""),"",J12-TODAY())It returns empty cell if no req date (column G) or order is closed (date in column M)
Conditional formatting:
All rules are expanded on entire range (not to generate bunch of new rules applying cell by cell with Format Painter) and one more rule on the top with "no color" if the cell is blank
Please check in attached file.
7 Replies
You may check if it is something in M11 or not by checking length of the value of this cell.
To return empty string to K11 you shall update your current formula for K11 with above condition.
Not to highlight K11 in such case you may add another conditional formatting rule on first position which check M11 length and apply Fill No color format K11.
SergeiBaklan Ok
1--take my formula that I am generating the value for K11 (=Days(J11, J3(the cell containing the current date))---then add to this formula to have it also check M11 so that it checks for anything there. if anything then the formula yields no valve and therefore no formatting kicks in. If any thing is there in M11 then go ahead and do the calculation and my formatting happens?? Can you provide any sample language? I don't know how to right it. Thanks....
Perhaps we may generate concrete formula if you provide small sample file.
