Find and Replace within Conditional Formatting Rules

%3CLINGO-SUB%20id%3D%22lingo-sub-2051582%22%20slang%3D%22en-US%22%3EFind%20and%20Replace%20within%20Conditional%20Formatting%20Rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2051582%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20way%20to%20%22Find%20and%20Replace%22%20all%20the%20refences%20to%20a%20different%20sheet%20within%20all%20my%20conditional%20formatting%20formulas.%20Find%20and%20Replace%20worked%20great%20to%20change%20all%20references%20within%20the%20spreadsheet%20itself%2C%20but%20failed%20to%20change%20in%20all%20of%20my%20conditional%20format%20rules.%20Is%20there%20an%20easy%20way%20to%20do%20this%20other%20than%20going%20in%20and%20manually%20changing%20them%20all%3F%20I%20will%20provide%20an%20example%20just%20in%20case%20I%20not%20explaining%20this%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOld%20Formula%20%3DISTEXT('%3CSTRONG%3ESHEET%201%3C%2FSTRONG%3E'!%24A1)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EDesired%20Formula%20%3DISTEXT('%3CSTRONG%3ESHEET%202%3C%2FSTRONG%3E'%24A1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20I%20am%20trying%20to%20highlight%20a%20range%20of%20cells%20based%20on%20a%20cell%20from%20%3CSTRONG%3ESheet%201%3C%2FSTRONG%3E%2C%20but%20need%20that%20changed%20to%20%3CSTRONG%3ESheet%202%3C%2FSTRONG%3E%20for%20about%2014%20different%20rules.%20Does%20that%20make%20sense%3F%20If%20more%20clarification%20is%20needed%20please%20let%20me%20know.%20Thanks%20for%20any%20advice%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2051582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052421%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20and%20Replace%20within%20Conditional%20Formatting%20Rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F925680%22%20target%3D%22_blank%22%3E%40Budnelman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20fear%20you'll%20have%20to%20edit%20the%20rules%20manually.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello All,

 

I am looking for a way to "Find and Replace" all the refences to a different sheet within all my conditional formatting formulas. Find and Replace worked great to change all references within the spreadsheet itself, but failed to change in all of my conditional format rules. Is there an easy way to do this other than going in and manually changing them all? I will provide an example just in case I not explaining this properly.

 

Old Formula =ISTEXT('SHEET 1'!$A1)  

Desired Formula =ISTEXT('SHEET 2'$A1)

 

Basically, I am trying to highlight a range of cells based on a cell from Sheet 1, but need that changed to Sheet 2 for about 14 different rules. Does that make sense? If more clarification is needed please let me know. Thanks for any advice in advance.

1 Reply

@Budnelman 

I fear you'll have to edit the rules manually.