changing the color of a cell for invalid values while copy/paste on a cell with "Data Validation"

[jt5721704]

Hello

I created a list data validation to a range of cells using =INDIRECT(SUBSTITUTE($D3," ","_")), and it works great, I do understand that there is a limitation that data validation only works with user enter values, however I need to populate the range with a Copy/Paste, so what I need, if possible, for a way that when I paste values I could flag, by changing the color of the cell, the invalid values pasted so the user will know that a new value is required to be selected from the drop down list.

If you have any ideas please share?

Thanks

 

[pbornemeier]

What constitutes an invalid value?
If it is an error message then you can select all errors using F5 | Special | Formulas | Uncheck all but errors
and coloring the background

This code will execute the above steps

    With ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas, 16).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

 

[jt5721704]

What constitutes an invalid value?

For example if the values in the validation list are Red, Yellow, Green and I paste blue. The process would change the color of that cell.

No error messages, just change the color of the cell.

Thanks

 

[pbornemeier]

Put this code in the codepage for the worksheet that contains the data validation cells:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    If Not Intersect(Target, ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
        For Each rngCell In Intersect(Target, ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation))
            CheckCellValueVsCellListValidation rngCell
        Next
    End If
End Sub

Put this code in a standard module

Option Explicit

Sub CheckCellValueVsCellListValidation(rngCell As Range)
    'Ensure rngCell has validation applied, range with no validation will cause error
    'Turn cell yellow if it has validation, but the current value is not valid
    Dim lX As Long
    Dim lCellCount As Long
    Dim bFound As Boolean
    Dim varList As Variant
    
    If rngCell.Validation.Type = 3 Then
        'List Validation
        rngCell.Interior.Color = -4142
        If InStr(rngCell.Validation.Formula1, ",") > 0 Then
            'List specified explicitly
            varList = Split(rngCell.Validation.Formula1, ",")
            For lX = LBound(varList) To UBound(varList)
                If CStr(rngCell.Value2) = CStr(varList(lX)) Then
                    bFound = True
                    Exit For
                End If
            Next
        Else
            'List specified by Named Range or Explicit Range
            lCellCount = Range(Mid(rngCell.Validation.Formula1, 2)).Cells.Count
            For lX = 1 To lCellCount
                If rngCell.Value2 = Range(Mid(rngCell.Validation.Formula1, 2)).Cells(lX) Then
                    'Formula1 looks like =NamedRange or =$AX$1:AX$12
                    bFound = True
                    Exit For
                End If
            Next
        End If
        If Not bFound Then
            rngCell.Interior.Color = vbYellow
        End If
        
    End If
End Sub

 

[jt5721704]

Thanks Phil.

Which values do I have to define. I know rngCell, any other values?

Thanks

 

[jt5721704]

Hello

I did define rngCell, and tested your code but it looks like the only valid value is the first value of the list. Any other valid value of the list if it is present in a cell will be flag (Yellow) as invalid.

Thanks for all your help.

 

[pbornemeier]

You should not define rngCell. The code checks to see if any of the cells that contain conditional formatting are changed then compares the value in them to the values in their validation list.

Did you install the code as specified, the first in the code page for the worksheet that contained the DV cells and the second in a standard module?

In the first piece of code, in this line:
For each rngCell In Intersect(Target, ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation))
Target represents cell or cells that were just changed for any reason
ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation) represents all cells on that worksheet that contain data validation
If any cells meet both of those criteria then each of them is sequentially defined as rngCell and passed to the second chunk of code which checks to see if the validation for that cell is a List type, how that List is defined and then compares the current value in that cell to the values listed in the validation for that cell. It works for me in all that I tested.

Please post the validation lists in the cells that are failing for you. You may have configured something in a way I did not plan for.

 

[jt5721704]

Hello Phil

Column C. The list has two values. A and B
Column D. The list validation has this formula "=INDIRECT(SUBSTITUTE($C2," ","_"))". If value A is selected in C, D will have a list of 4 values, if value is B the list will have 7 values.
Column E. The list validation has this formula "=INDIRECT(SUBSTITUTE($D2," ","_"))". Same situation but more combinations because the list will change base on the selected value in C, for which there are 11 options.

I do not know if important but I build the sheet using a macro. The macro take information from sheet 1 and create sheet 2 within the same workbook.

I do not know if I could attach the spreadsheet so you can take a look.

Anyway, I do appreciate all your help

Thanks

 

[pbornemeier]

I considered the cases where a list could be defined explicitly: "1,2,3,4"
Or by a named range "=NamedRange"
Or by an explicit range "=A5:A10"

What is in C2?
If C2 contains "A" what does "=INDIRECT(SUBSTITUTE($C2," ","_"))" evaluate to?
If C2 contains "B" what does "=INDIRECT(SUBSTITUTE($C2," ","_"))" evaluate to?

 

[jt5721704]

Sorry. My mistake. I meant Column C range.