Change Conditional Formatting formula for all rules in cell

[KrisKiel]

Is there a way to change the reference cell for all conditional formatting rules in a single cell?

I’m looking to speed up a gruelingly redundant process while building a spreadsheet. It will take me over 10 hours the way I’m doing it now.

There’s 640 cells and each has 10 conditional formatting rules in them to change the cell’s color based on another cell’s value. I need to change the cell each one is linked to.

Formula = ‘Direct Training’!$B$3=1
Formula = ‘Direct Training’!$B$3=2
etc. through to… =10

EDITING TO ADD: Macros would be fine as a solution for this if they can be completely removed afterwards.

 

[etaf]

There’s 640 cells and each has 10 conditional formatting rules in them to change the cell’s color based on another cell’s value.

you could use indirect() to change the cell reference

Not 100% following what you are after

here i have used sheet1!B1 - which has the cell i want to link to in
in this case in sheet1B1 I have sheet1A1
and so now the value in sheet1A1 - which is a value of 12 is then tested against the main sheet K2 and if that is also 12 , then it changes colour

is that the sort of thing you are after

indirect() is a volitile function - so may slow the spreadsheeet down if used a lot

Book1.xlsx
	J	K	L
1		12
2		12	sheet1!a1
3
4
5			TRUE
New Worksheet

Cell Formulas
Range	Formula
L5	L5	=K1=INDIRECT(L2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2	Expression	=K1=INDIRECT(Sheet1!B1)	text	NO

 

[bebo021999]

Are the 640 cells got same conditional formating formula structure?
If so, try manual correction for 1 cell then apply to whole 640 cells?

 

[KrisKiel]

Are the 640 cells got same conditional formating formula structure?
If so, try manual correction for 1 cell then apply to whole 640 cells?

I did that to help with part of the issue (applying the color rules for the different cell values). But I still need to now change the referent cell in the formula.