Conditional formatting is one of those features that seems simple until it isn't. The user who posted this question did exactly what most of us would do: set a rule in one cell, drag it down, drag it across, and expect consistency. Instead, they got a patchwork of correctly formatted cells and stubborn holdouts. That frustration is familiar, and it points to a misunderstanding that trips up even experienced spreadsheet users.
The root cause here is almost certainly a mixed reference problem. When you write a conditional formatting formula like `=AND(F7>=O7, G7<=O7)` and then drag it across columns, the references to F7 and G7 shift relative to the new location. The formula in P7, for example, becomes `=AND(G7>=P7, H7<=P7)`. That might work in some cases, but it breaks the logic entirely when the column offset pushes the date references outside your intended range. The user's starting point in O7 was correct, but the drag operation didn't preserve the column anchors needed for the start and end dates.
The practical fix is straightforward: use absolute references for the date columns. Lock F and G with dollar signs so the formula reads `=AND($F7>=O7, $G7<=O7)`. This keeps the date lookups fixed while allowing the comparison cell, O7, P7, Q7, and so on, to shift naturally as you drag. Apply the rule to the entire range at once, and the conditional formatting engine will evaluate each cell against its own position relative to the locked dates. No more guessing, no more inconsistent highlighting.
What this episode really reveals is a gap in how most spreadsheet tools teach conditional formatting. Users learn the visual result, not the reference logic that drives it. The formula bar looks like code, but the behavior feels like magic until you understand relative versus absolute addressing. That knowledge gap is exactly where AI-native spreadsheets can step in. Imagine a tool that watches you drag a rule, detects the broken references, and suggests the fix before you notice the problem. Or one that lets you describe the formatting goal in plain language, "highlight cells where the date falls between these two columns", and writes the correct formula for you. The technology exists. The question is whether the tools we use will meet us where we get stuck, or leave us hunting through Reddit threads for a solution that should be obvious.