Solved: Validating Excel data entry

[Duncs]

I have a spreadsheet into which staff are required to enter information. Some information must be entered, and where values are entered, other information must also be entered.

So, for example, I have the following:

In Columns A -> I I have the following headings:

Serial Num
Removed (Y/N)
Num Removed
Unit Cost
Removed By
Date Removed
Num Left
Reordered (Y/N)
Date Reordered

Staff would enter the serial number, and if a 'Y' is entered in column B, they must enter a value in Column C.

Similarly, if a 'Y' is entered in Column H, a value must be entered in Column I.

What I want to know is, without the use of a data entry form, is it possible to incorporate this kind of error checking in an Excel spreadsheet? Is it also possible to code it so that cells are greyed out if they are not required, coloured if they are, and flagged if they need an entry?

Duncs

[RonMcK]

Duncs,

Perhaps you can get some ideas from the attached file. I used data validation to control the answers used in col B and H, and conditional formatting to color code exceptions in columns B and H (missing entry) and C and I (not needed, missing entry, entry without related Y/N answer). You can adjust colors and logic as required.

Cheers!

[mdmackillop]

You can add code to prevent closing unless cells are filled.
Based on Ron's example

[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Dim MyAdd As String
Set Rng = Union(Intersect(Columns(2), ActiveSheet.UsedRange), Intersect(Columns(8), ActiveSheet.UsedRange))
For Each cel In Rng
If cel = "Y" And cel.Offset(, 1) = "" Then
MyAdd = MyAdd & cel.Offset(, 1).Address(0, 0) & vbCr
End If
Next
If Len(MyAdd) > 1 Then
Cancel = True
MsgBox "Enter data in cells" & vbCr & MyAdd
End If
End Sub
[/VBA]

[RonMcK]

And, based on Malcolm's code, you can also alert the user to two other cases that need correction (a No entry with data following and a data cell with neither a Yes or No answer preceding).

Cheers!

[Duncs]

Many thanks for your replies. I think I was trying to over-complicate matters, looking at alerts if the field is blank etc. Conditional formatting does the job just as good.

Cheers

Duncs

[RonMcK]

Glad to be able to help.

Cheers!