Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up a column on change of another
Hello Everyone,
I need your help with regarding two matters, The First --------- User can update column C and D entered from a drop down list box (data, Validation, List). There are 5 different combinations of column C and D.According to those combination columns E's value has to automatically update. C D E Hot meal Item Out Green Cold meal Item Out Green Hot meals Item In Blue Cold meals Item In Blue Drinks Out Yellow And so on When a certain rows are empty, Column E should also be empty. The Second ---------- A B D H I J K Date Flight Out/ Total Total Return Return No. In Per Per Per Per Series Flight Series Flight 10.01.05 878 Out 6 2 10.01.05 878 Out 10 16 3 5 10.01.05 878 In 15 5 10.01.05 878 In 10 4 10.01.05 878 In 10 35 9 18 10.01.05 578 Out 15 5 10.01.05 578 Out 12 27 3 8 10.01.05 578 In 20 6 10.01.05 578 In 20 40 4 10 11.01.05 878 Out 6 2 11.01.05 878 Out 10 16 3 5 11.01.05 878 In 10 4 11.01.05 878 In 10 20 9 13 11.01.05 578 Out 15 5 11.01.05 578 Out 12 27 3 8 11.01.05 578 In 20 6 11.01.05 578 In 20 40 4 10 Every Change in Date (Column A), Flight No (Column B), Out/In(Column D), Column H's figures should total up to Column I last line. Like wise Column J's figures should total up to Column K. I think you can get the result from "Pivot tables". But I prefer to write a code that's hassle free to the user. Further I have this coding in the worksheet already works perfect. Please advice me how to bring the new coding in. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range, cell As Range, bFound As Boolean If Target.Count 1 Then Exit Sub If Target.Address = "$G$2" Then Cells.Interior.ColorIndex = xlNone 'To select the whole column of the "Seals From" Set rng = Range(Cells(5, 6), Cells(Rows.Count, 6).End(xlUp)) bFound = False For Each cell In rng If cell.Value <= Target And cell.Offset(0, 1).Value = Target.Value Then If Not bFound Then Cells(cell.Row, "J").Select bFound = True End If cell.EntireRow.Interior.ColorIndex = 6 End If Next End If End Sub Thank you very much in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
disable Total and/or Sub total for a single column | Excel Worksheet Functions | |||
DIVIDING TOTAL CELLS IN COLUMN BY 16 TRYING TO CHANGE WEIGHT OZ TO | Excel Worksheet Functions | |||
Change Word Total and Grand Total | Excel Programming |