Conditional Formatting Defaulting to Absolute Reference ($)

Spicoli21

New Member
Joined
Sep 28, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,284
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,043
Messages
5,767,797
Members
425,436
Latest member
MSPaperclipMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top