Ensuring Validation sticks to a range

[Djarn]

I'm trying to make it so that validation sticks to a range.

for example I want everything in column B to only allow input that corresponds to the data in my pick list.

So Range("B:B")
My picklist or range of data that's valid being S1:S10

If someone cuts a cell anywhere in Cell B and places it elsewhere, whatever cell was cut will not be blank, the validation requirements will be reapplied to Range("B:B")

Thanks for your help in advance

[:) Sixthsense :)]

Do right click on sheet tab and select view code and paste the below code. Return to excel and check

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub

With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=$S$1:$S$10"
End With

End Sub

[noboffinme]

Hi Djarn,

A suggestion would be to apply the Conditional Formatting (CF) via VBA instead of the usual way using a Formula in the standard CF feature.

I've had issues with CF 'moving' when Cells values move & this solved the problem for me.

Hth

[:) Sixthsense :)]

I've had issues with CF 'moving' when Cells values move

Can you please brief the above quoted sentence for my better understanding

[noboffinme]

Hi Sixthsense,

I had created a report for a Colleague using Formulas with CF & found an unexplained range change happening with the original CF 'Applies to' ranges.

On further investigation, I found that the colleague was inserting & deleting Rows which also changed the CF ranges & the report started to format in strange ways due to this.

I also could not replicate the error until I found out that insert/delete was happening...

So, my solution was to just format based on cell values using VBA & it solved the problem immediately.

[:) Sixthsense :)]

Make use of $ symbols in Reference Or Indirect Function to keep the STATIC reference.

[Djarn]

I appreciate the help

I'll try the code out later, it's really similar to what I tried but I see one or two differences, many thanks again

[:) Sixthsense :)]

Yes please try it and if you don't get the better results then please attach a file so that it will be helpful to fix the issue