Conditional Formatting Defaulting to Absolute Reference ($)

[Spicoli21]

I am trying to do a value comparison across hundreds of rows. For each row I have a specific upper and lower bound that each cell in that row needs to fall between. If the value in a cell doesn't fall within those bounds, I want that cell to be highlighted. I am using Conditional Formatting and my problem is that I want to use relative referencing and Conditional Formatting keeps defaulting to absolute reference ($). Writing my own formula doesn't work and neither does using the formatting rule options that Excel provides. 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.

 

[cmowla]

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!

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.

 

[Peter_SSs]

Welcome to the MrExcel board!

I am trying to do a value comparison across hundreds of rows.

Could you mock up a much smaller sample of data and post that?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Then explain exactly what you are trying to do in relation to that smaller sample.