VBA Programming!

I have an excel 'project' which has about 12 different sheets plus the "Main" one that contains personnel Leave days.

In one of my sheets, I have a button containing this macro:

Please Login or Register  to view this content.

This macro will simply allow the user to select ANY cell and upon pressing this button it will change the interior of the cell/s to blue, thereby letting everyone know that those are that individual's leave period.

Right now I am doing the modifications to the leave days remaining manually on the "Main" page which in turns updates the rest of the sheets automatically, but what I really want is this:

Upon making the selection/s of cell/s and when they press the button containing that macro above, I would like the macro to update the individual LeaveTaken so that everything gets automated.

Example:
I have 20 days leave annualy and I will be taking 5 days leave only.

I select under my name "Salmerin" about 5 cells and I click on the Annual button, the 5 cells will change the interior color to blue on the active sheet and on the Main sheet it will search for "Salmerin" row and replace the number under the 'Leave Taken' cell with the 5 which the remainder will be displayed as 15 and so will the rest of the sheets after that.



Any ideas? Thanks

Hi

1) Is the "selection" to be colored going to be rows, columns or both
2) Which column on Main contains the name?
3) Which column on Main contains the leave taken?
4) Is the "remainder" formula based and will process automatically, or does this have to be adjusted. If it has to be adjusted, then which column contains this number.

Perhaps a small example file showing your layout / relevant code would help.

rylo

Hi

See it this gets you going

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

See it this gets you going

Please Login or Register  to view this content.

rylo

WOW!!!! It worked just fine!
Thank you so much Rylo!

Hi

Here goes as requested. I've put some comments for each line

'dimension the variables
Dim FindIT As Range, DayRng As Range, Scnt As Long

'create an object that is the position of the find function. If you have a selection, then it you can find the row. The cells(selection.row, 1) will give you the value in A1 of your selection and this is what you are searching for.
Set FindIT = Sheets("Main").Range("A:A").Find(what:=Cells(Selection.Row, 1).Value, lookat:=xlWhole)

'Set another object range that covers the days from row 3 that matches the selection. Cells(3,selection.column) will give the first cell. Resize(1,selection.columns.count) gives a 1 row x n column array that is the same size as your selection. Effectively selects the days from row 3 that matches your selection range.
Set DayRng = Cells(3, Selection.Column).Resize(1, Selection.Columns.Count)

'uses the countif function to find the number of "S" items in the "days" range
Scnt = WorksheetFunction.CountIf(DayRng, "S")

'goes 2 columns to the right of the findit range object, and adds the number of cells in the selection less any "S" items to the number that was in that cell.
FindIT.Offset(0, 2).Value = FindIT.Offset(0, 2).Value + Selection.Columns.Count - Scnt

If that doesn't make sense, don't hesitate to come back again.

rylo