Invalid Data in Validation Cells


New Member
I have created a product order form on Excel.
In cell A1 I can select a product from a validation list.
in cell A2 I can select from a choice of upholstery for that specific product from another validation list.
in cell A3 I can select from a choice of weights for that specific product from a further list.
Upholstery and weights can differ according to which product is selected.

Whilst I have written a macro to clear all data between orders I am worried that the person who places the orders might not clear the data and then select a different product which would potentially leave the previous upholstery and weight in the cells below creating an ‘illegal’ combination and an invalid order.
i discovered circle invalid data but it appears that is not an automatic function and it would need to be selected each time. Is there any way such an illegal combination would be automatically highlighted?

Any help appreciated.


MrExcel MVP
Use a Worksheet_Change event macro to automatically clear the dependent dropdowns when a parent dropdown changes.

To install this code in the Worksheet's code module:

  • Right-click on the sheet tab
  • Select View Code from the pop-up context menu
  • Paste the code below in the sheet's code module

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=darkblue]Then[/COLOR]
    [COLOR=darkblue]ElseIf[/COLOR] Target.Address(0, 0) = "A2" [COLOR=darkblue]Then[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...