Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Conditional Formating. NEED ANSWER TO FINISH PROJECT!
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(D4:D10, 1) C2=4 (A3) Formula =SMALL(D4:D10, 2) C3=5 (A2) Formula =SMALL(D4:D10, 3) C4=5 (A5) Formula =SMALL(D4:D10, 4) C5=6 (A1) Formula =SMALL(D4:D10, 5) C6=8 (A5) Formula =SMALL(D4:D10, 6) C7=9 (A6) Formula =SMALL(D4:D10, 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...... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Conditional Formating. NEED ANSWER TO FINISH PROJECT!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Answer | Excel Discussion (Misc queries) | |||
Is Conditional formatting the answer | Excel Discussion (Misc queries) | |||
Conditional Formatting not giving correct answer... | Excel Worksheet Functions | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
Help me finish my project please! | Excel Programming |