You are here:

Excel/Macro to check proper values are present in excel sheet before saving


QUESTION: "Hi Gulshan,

I am working on a excel sheet wherein there are 5 drop down cells namely (A2,B2,M2,N2,P2). Now based on the selections in cells A2 & B2 the drop - down list in the remaining three cells gets populated and the user can select from the available list.

For a single combination in cells in A2 & B2 there can be multiple possibilities in the remaining cells

For example:-
A2:- Fruit B2:- Apple  M2:- Red    N2:- Sweet P2:- small
A2:- Fruit B2:- Apple  M2:- Green  N2:- Ripe  P2:- Big
A2:- Fruit B2:- Apple  M2:- Reddish white  N2:- Natural P2:- vary

As per the above example for that combination (A2 & B2) the user can select any of the three values from the drop down list.

The problem am facing is if user changes the combination in A2 & B2 and doesn't change the selection in the remaining three drop-downs or copy pastes the values and mismatch occurs then my entire excel sheet is messed up.

For example (Mismatch)
A2:- Fruit B2:- Apple  M2:- Red  N2:- Sweet P2:- small
A2:- Fruit B2:- Apple  M2:- Red  N2:- Ripe  P2:- Big  OR
A2:- Fruit B2:- Apple  M2:- Red  N2:- Sweet P2:- vary

Appreciate if you could help me with a macro that checks the entire sheet for such possible errors and ensures that proper entries are made for the combinations in when user clicks On Save (On_Save event) and gives an error message or highlights the error row or cell.

P.S:- The data range is saved in Sheet2 of the same work book.

Any help would be appreciated.

Thanks & Regards

ANSWER: Dear Sourabh,

This can be handled in excel even without using macros. We can use the conditional formatting and set up the necessary rules.

For Example, you can set up the following conditional formatting in cell M2:
1. Goto Format - conditional Formatting
2. Select first drop box as "Formula is"
3. Enter the formula as: =IF(AND(A2="Fruit", B2="Apple", M2<>"Red", N2<>"Sweet", P2<>"Small"),"true","false")
4. Setup the formatting if this condition is true - you could highlight the cell in red.
5. You can add as many conditions to cell M2 as you need. You can set up similar conditions for cells N2 and P2 as well.

By doing this, you will be able to see any discrepancies immediately and dont need to wait till file save.

If this doesn't meet your requirements, we can create a macro.

Hope this helps

---------- FOLLOW-UP ----------

QUESTION: Hi Gulshan,

Thanks for the quick response. I wanted to know my existing drop - down cells are already using Data validation rules and populating the values from sheet2 based on those rules and range.

So is it possible to have another formula for the same cell??


Hi Sourabh,

Formula, data validation and conditional formatting are independent functionalities. You can go ahead and setup conditional formatting for a cell irrespective of whether it has data validation or formula.
When you set up conditional formats, you are not entering the formula in the cell, but you are only setting up a format for the cell which will apply only if the condition is satisfied.

So feel free to setup the approach I suggested and see if it works.

I hope I have answered your question.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 All rights reserved.