Forum Discussion
Solved
Conditional format cells in spilled ranges as formulas
Hi! I recently learned that I can conditionally format a range by using the formula =ISFORMULA(topLeftCellOfRange), though, I also then learned it doesn't work on spilled ranges, like when spilli...
Your formula doesn't make the cell blank, it returns empty string. So far in Excel formulas don't return blank as a value.
Thus if the purpose is to color all cells with any value, includes empty string, you may apply rule like
=ISTEXT(N5) + (LEN(N5)>0)
Another approach is to test whether each conditionally formatted cell intersects the spilt range or not. That is, to use a conditional format based upon the formula
= isDA?
which refers to
= ISREF(thisCell spilt.range#)
where
'thisCell' is a relative reference to the current cell (=RC in R1C1 notation)
'spilt.range' is the anchor cell of the calculation range.