Global Change for Conditional Formatting

[grayti]

I have a workbook three sheets

In the first tab 17860ProfSvcs I have 26 conditional formatting rules.

Each with a formula similar to this
=IFERROR(INDIRECT("'17880Software'!"&SUBSTITUTE(ADDRESS(1,MATCH($B$51,'17880Software'!$A$51:$CA$51,0),4),1,ROW()-1)),0)+IFERROR(INDIRECT("'17870Hardware'!"&SUBSTITUTE(ADDRESS(1,MATCH($B$51,'17870Hardware'!$A$51:$CA$51,0),4),1,ROW()-1)),0)+$B58>0

I would like to use Format Painter to bring the conditional formats 17870Hardware and 17880Software. Once on the new sheet I will have to make a changes to each of the 26 rules so it works on that sheet.
The change that would be needed when moved from 17860ProfSvcs to 17870Hardware is that 17870Hardware in the formula would have to become 17860ProfSvcs.
Here is the formula that would be needed for the Conditional Format rule on the 17870Hardware tab
=IFERROR(INDIRECT("'17880Software'!"&SUBSTITUTE(ADDRESS(1,MATCH($B$51,'17880Software'!$A$51:$CA$51,0),4),1,ROW()-1)),0)+IFERROR(INDIRECT("'17860ProfSvcs'!"&SUBSTITUTE(ADDRESS(1,MATCH($B$51,'17860ProfSvcs'!$A$51:$CA$51,0),4),1,ROW()-1)),0)+$B58>0

Is there a global way to make the change to all 26 rules?

 

[Micron]

I think that you ought to be able to do this with VBA. This will return the cf formula on Sheet1 range B3 (which is shown on the next line)
sheets("Sheet1").range("B3").formatconditions(1).formula1
=AND($B1=$B2,$D1<>$D2)

Then use the Replace function to swap the sheet names. You actually have 26 rules in one cell? I thought the limit was 3.
Anyway, the idea should work as far as I can tell. If you have more than one rule for any cell, then I suppose you'd have to loop over them. The end of the counting would be the number of cf conditions, which can be known from
sheets("Sheet1").range("B3").formatconditions.count

 

[Micron]

addendum:
I have no idea if you can use the format painter or not. Should be able to code to copy font characteristics, cf cell colouring, etc. in the vba procedure and apply them to the target cell. However, if painter works that would be so much easier.