Data Validation: Global Search & Replace

[Saighead]

Hi,

I have the same data validation formula set for the range B:B on a couple of hundred worksheets. I need it replaced throughout the workbook by another formula. Is there a way to do it quickly with a macro or something? Changing the thing by hand will take half a day at least...

Thanks in advance.

 

[mart37]

Sub ChangeCF()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Set Rng = ws.Columns(2).SpecialCells(xlCellTypeAllFormatConditions)
    With Rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=A1>30"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
Next
End Sub

 

[Saighead]

Thanks for the input, but it's not conditional formatting that I'm trying to change. It's about data validation (ribbon: Data > Data Validation > Settings). The formula I have now prevents identical entries but it's case-insensitive. I'm looking to do a global search in all worksheets and replace it with a case-sensitive one.

 

[mart37]

sorry, here the code your validationchange

Sub ChangeDV
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Set Rng = ws.Columns(3).SpecialCells(xlCellTypeAllValidation)
    With Rng.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=formula"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Next
End sub

 

[mart37]

You use now something like :=countif(c:c,c1)=1

 

[mart37]

Maybe: =SUMPRODUCT(--ISERROR(FIND(B1,B:B)))=1048576-1

 

[Saighead]

It took just a few seconds to modify every worksheet in the file. Thanks a lot, I really appreciate your help, you've saved me a lot of hassle.