Validation

M

Mohad Haddad

Dear sir
First I would like to thank you about this web-site it's so helpful,
If u can help me, I want to make a program on oracle and excel is part
of it I will give u an example about what I need to do in excel:
Suppose I have 4 cells A1 B1 C1 D1, in D1 there is a fixed value lets
say 10
I want a restriction that limit the user not to enter a value in A1 B1
C1 cells but less than or equal the value in D1 that's men
A1+B1+C1<=D1
And if the user enter a value in one of the three cells that make the
summation of three cells grater than D1=10, then must display a
message and return the cursor to the cell that make the error and
color it with red
That's all I need, I will be grateful if u help me in this issue
Best regards
Mohad Haddad
 
D

David McRitchie

Hi Mohad,
Directions to install:
Unlike regular macros which are installed in regular modules,
Worksheet Events are installed with the worksheet by
right-clicking on the sheettab, choose 'view code', and then
paste in your macro.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' If Target.Row <> 1 Then Exit Sub 'use to limit to row 1
If Target.Column > 4 Then Exit Sub 'limited to A:D columns
Intersect(Range("A:D"), Rows(Target.Row)).Interior.ColorIndex = xlNone
Intersect(Range("A:D"), Rows(Target.Row)).Font.ColorIndex = xlAutomatic
Dim a As Double
On Error Resume Next
a = 40
a = Target.Offset(0, 1 - Target.Column) + _
Target.Offset(0, 2 - Target.Column) + _
Target.Offset(0, 3 - Target.Column)
If a <> Target.Offset(0, 4 - Target.Column) Or a > 20 Then
Target.Font.ColorIndex = 3
If IsEmpty(Target) Then Target.Interior.ColorIndex = 6
End If
End Sub

Whether English is you native language or not please don't
misspell words that you know how to spell it makes it harder
for everyone to read. For many people in this newsgroup
English is not their native language.
 
Top