Forum Discussion
Solved
Conditional format malfunction
I have a conditional format that is sort of working. The format is applying the borders seen below. It should be applying them to all the cells to the right of columns A & B for all rows where colu...
Currently it's =OR(NOT(ISBLANK(B7:B100))) as the range will grow or shrink if more data is added. Also once it malfunctions, it won't change again, even if I delete the conditional formatting rule. It's very bizarre.
Use the formula proposed by SergeiBaklan. Excel will automatically adjust it when it applies the rule to the rows below.
- Thank you SergeiBaklan, HansVogelaar.
This has corrected the issue. I spent a lot of time trying to get the right formula from many different sites dedicated to excel and these sorts of things. This answer was not on any of those sites.You are welcome.
Simple way to check if rule works is to imitate its behaviour in cells. Let say you are going to apply the rule to the range $C$7:$I$100. Stay on empty cell outside above range, for example in cell C105, and enter into the rule formula. In our case =B7<>"".
Now drag C105 to the right and to the down and check where it shows TRUE or FALSE. Conditional formatting iterates the cells approximately the same way, and apply formatting for each cell where rule formula returns TRUE. Otherwise ignores the cell.
In C105 you see formula result for C7; in C106 - for C8; etc.
