Another Conditional Formating. NEED ANSWER TO FINISH PROJECT!

[Albert Mulder]

Thank you for all who have help so far. I have two things left.


(Issue Number 1: Need Cell to only take a one diget number 0-9
)
I have a row with 7 Cells each cell will contain a number. The user will
enter a number into each one, but I only want the user to be able to enter a
number 0-9

Example:
A1=6
A2=5
A3=4
A4=5
A5=8
A6=9
A7=0

(This I have working)
After that I have a row with 7 Cells. I need these cells to sort lowest to
highest.

Example:
C1=0 (A7) Formula =SMALL(D410, 1)
C2=4 (A3) Formula =SMALL(D410, 2)
C3=5 (A2) Formula =SMALL(D410, 3)
C4=5 (A5) Formula =SMALL(D410, 4)
C5=6 (A1) Formula =SMALL(D410, 5)
C6=8 (A5) Formula =SMALL(D410, 6)
C7=9 (A6) Formula =SMALL(D410, 7)

(Got this working also)
After that I have a row with 7 cells. I need these cells to take the lowest
number and enter it into E1, then take the next highest number and subtract
the last lowest number and enter it into the next cell. But if the answer = 0
then enter an X there instead and in red text else change to black text.

Example:
E1=0 So it changes to X
E2=4
E3=1
E4=0 So it changes to X
E5=1
E6=2
E7=1

(Got only part of this working, I can change the cell that returnes (1) but
cant figure out how to have it auto check back to the first cell that caused
it. (The A Cells) I need to be able to tell the user that they need to change
the number they have entered else the formula the have will not work.)

Now after that I need
If any answer = 1 then change that Cell to red along with the original
number that corresponds with it from Cell A1-A7. <-- This last part.
Example:
Cell E3 and A2 would be red cells
Cell E5 and A1 would be red cells
Cell E7 and A6 would be red cells


(Would I need to redo my whole formula? If need be I can just have Cells
A1-A7 & E1-E7) I know it can be done but just don't know how.

PLEASE HELP......

 

[Per Jessen]

Hi

See comments below

(Issue Number 1: Need Cell to only take a one diget number 0-9
)
I have a row with 7 Cells each cell will contain a number. The user will
enter a number into each one, but I only want the user to be able to enter
a
number 0-9

Example:
A1=6
A2=5
A3=4
A4=5
A5=8
A6=9
A7=0

Select A1:A7 > Goto Data > Validation > Allow: Whole number > Data: between

Minimum: 0 > Maximum: 9 > Ok

(Got only part of this working, I can change the cell that returnes (1)
but
cant figure out how to have it auto check back to the first cell that
caused
it. (The A Cells) I need to be able to tell the user that they need to
change
the number they have entered else the formula the have will not work.)

Use column F as helper column and enter the formula from column E and
calculate on the unsorted data. The column can be hidden if desired.

Rightclick on the sheet tab and select wiew code, and insert the code below
in the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
Set TargetRange = Range("A1:A7") ' Change range to suit
Set isect = Application.Intersect(Target, TargetRange)
If Not isect Is Nothing Then
If Target.Offset(0, 5).Value = 1 Then
Msg = MsgBox("Change the value in row : " & Target.Row,
vbInformation, _
"Warning !") ' Change the text as desired
End If
End If
End Sub

Now after that I need
If any answer = 1 then change that Cell to red along with the original
number that corresponds with it from Cell A1-A7. <-- This last part.
Example:
Cell E3 and A2 would be red cells
Cell E5 and A1 would be red cells
Cell E7 and A6 would be red cells

To make cells turn red use conditional formatting.

First select E1:E7, goto Format > Conditional formatting > Cell value Is :
equal to : 1 > Format as desired.

Select A1:A7 goto Format > Conditional formatting > Formula is > =F1=1 >
Format as desired.

I hope it helps.

Best regards,
Per