Data Validation to only allow a 8 digit HEX value on several cells.

[Krulle1000]

Hello,

I'm trying to use data validation to validate if the user provides a 8 digit HEX value.

In example data validation in cell K2:
Allow: Custom
Formula: =AND(LEN(K2)= 8;ISNUMBER(HEX2DEC(K2)))

This works but I can't manage to replace the formula with ROW() in order to use it for all my data validation cells.
Formula: =AND(LEN(INDIRECT("K"&ROW()))= 8;ISNUMBER(HEX2DEC(INDIRECT("K"&ROW()))))

Excel returns next error: 'The Formula currently evaluates to an error'

What is wrong here?

 

[AlphaFrog]

I don't think you need the INDIRECT function. If you put your original formula in row 2 and drag-copy down, the formula will automatically adjust the relative references in the formulas for each row.

 

[Krulle1000]

Edit

 

[Krulle1000]

Thanks AlphaFrog
Indeed, the drag-copy down adjusts the formula with the relative references.
Now, if I would like to apply the data validation to several ranges (with slightly different formulas) by using vba code.
I assume that this could be done by using the Range.AutoFill method?

 

[AlphaFrog]

You could use VBA autofill. Alternativelly, if you apply the data validation to the entire range, the relative references will adjust.

    [color=darkblue]With[/color] Range("[B]J2:J100[/B]").Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AND(LEN(K2)= 8;ISNUMBER(HEX2DEC(K2)))"
        .IgnoreBlank = [color=darkblue]True[/color]
        .InCellDropdown = [color=darkblue]True[/color]
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = [color=darkblue]True[/color]
        .ShowError = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]