Forum Discussion
Vehicle Management help required
Dear Members,
I have a few vehicles, which we give out on rent. Need to automate a small process to make things easier for the staff.
Our file is as shown below (i am not able to paste the file image here)
Will try to explain.
In each of the rows, we have each vehicle with its number in the next column.
For eg: camry in B9 and its number in C9. Other Camry in B10 and its number in C10.
Like that, we have all the vehicles listed in B and C columns
The top row will have all the dates listed in each column. Like- 25th sep in D4. 26th sep in E4. 27th sep in F4... etc.
Whenever an order comes in and we book a vehicle, we block that vehicle in all the days that booking is for. For eg: one camry is booked from 5th oct to 20th oct, we highlight that row of that camry from 5th to the 20th oct in some color. So, we know that that camry is booked for those dates.
Similarly for all the vehicles in whatever dates they are booked for.
I want it in such a way that- when i click on any date, excel should check all its below columns under that date if the row is highlighted. If it is not highlighted means that vehicle is available. That available vehicle and its number should be highlighted in green.
I could just create a button through the developer option. But couldnt make out what condition to write in the 'CODE' section. Pls tell me from that point. You need not elaborate on 'CREATING A BUTTON'.
And after it is successful, i should be able to easily copy this button on to all other dates as well instead of CREATING a button for each date which will be cumbersome
Thank you very much for your time and support
12 Replies
- If you use the "Open full text editor" you can paste an image.
At least you need to tell us...
1. Whether the colors are added using Conditional Formatting or if colors are
added manually.
2. What color Green is it? The RGB numbers or The Color number or
the Color Index or is it vbGreen. (all users must use the same color green!)
3. Would double-clicking a header date instead of using a button be OK?
4. When the row with a color green is found, what should happen?
'---
'Nothing Left to LoseThank you very much for your response Sir.
and Thank you very much for your suggestion to use that 'Full Text Editor'. I did not get that option as i was using the phone. got it when i switched to my laptop
Here is my file (seems i don't have the permission to load images)
1) I intend to use 'conditional formatting to colour the cells, that - as soon as they type something, the cells are highlighted
2) No problem- All users will use the same green. Because i am making the file here, now. after i am successful in programming all this, i will distribute the file to my staff. So, everything is automated, they will not have the need to meddle with the colours.
I will give the following colours- for the red- among the Cell highlight colours of Excel, there is a colour called "ORANGE, ACCENT 2, LIGHTER 60%"
For the Green- "GREEN, ACCENT 6, LIGHTER 60%"
3) Double click, single click, button anythig is good. as long as it serves the purpose
4) When a car (row) with a colour green is found, that is it. that is the end of it all. The staff will know, what all cars are available on that particular date. They will take their own specific action while discussing with the customer.
The case is that: A customer will call in asking for a vehicle, on a particular date / a set of dates. So, my staff, as soon as they click on that date, they should be able to EASILY find out, what are all the available SUVs / Sedans, etc on that date without having to look for it in each column and row. and that will be easy if all the available cars are automatically highlighted in green / whatever colour.
This is just my idea. Am open to any more suggestions to make things easier for the staff.
FWIW, your post was sent at 3:00 pm and I rec'd it at 7:00 pm (USA west coast).
I am going to assume you want the worksheet to scroll up so that the first green row with available auto is in the 3rd row below the frozen two row header.
Also, I assume the staff will use the shortcut Ctrl + Home to return the worksheet to the top.
And, what row is the double-click row: first or second row.
What I don't understand is how this works if the potential custom asks for a Honda sedan on Oct 10. Do you just give him his choice of what is available on that date?
Also, please resolve this: "If it is not highlighted means that vehicle is available.
That available vehicle and its number should be highlighted in green."
More: please identify the column numbers or letters to be double-clicked.
Finally, if the worksheet is shared none of this will work.
(I see two people trying to scroll to different rows on the same worksheet)
There will probably be more questions.
Something to look at would be helpful.
'---
Nothing Left to Lose
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
(free excel programs)
