Is there some setting somewhere that I can activate or deactivate that will prevent Conditional Formatting from automatically defaulting to absolute reference? I also tried putting the bounds I need into separate cells and comparing each cell to those bounds. The "Applies To" box ends up switching to Absolute Referencing as well.
I doubt it. Because the very nature of relative vs absolute is to be able to put a formula in a cell "carry the formula down" so that it does repetitive (possibly iterative) calculations for you, once it's been created. The nature of conditional formatting is to specify a set of cells to do the exact same task to. The task/rule doesn't change relative to where it's being applied.
Also, you are not limited to how big of a range that the rules applies to, and so from that aspect, you can apply the rule to as big of a range as you want.
If you want to have dynamically changing "applies to" ranges (where you can have some helper columns contain dynamically changing information from which VBA can read from and then change the existing (or create new) conditional formatting rules), then that can be done with VBA.
The only way I know of that you can programmatically change the "applies to" range is with the following code. You can see I tried to remove the $, but if you run this code (If you don't know how to insert code into an Excel Workbook already, follow steps 1-4 of
Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com .) and press Ctrl G to see the immediate window (or simply manage rules from the menu, it doesn't get rid of the $ signs!
VBA Code:
Sub Failed_Test()
Dim ws As Worksheet
Dim CFrule As FormatCondition
Dim Rng As Range
For Each ws In Worksheets
Set Rng = ws.Cells
For Each CFrule In Rng.FormatConditions
Debug.Print "____________________"
Debug.Print CFrule.AppliesTo.Address
CFrule.ModifyAppliesToRange Range(Replace(CFrule.AppliesTo.Address, "$", ""))
Debug.Print CFrule.AppliesTo.Address
Next CFrule
Next ws
End Sub
But changing the cell references in Formula1 and Formula2 from absolute to relative can be done with VBA.