Sep 25 2022 05:49 AM
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
Sep 25 2022 06:38 AM
Sep 25 2022 02:57 PM
Thank 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.
Sep 25 2022 08:49 PM
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)
Sep 27 2022 12:59 AM
There is only one staff and he will use this file. So no worries about sharing :beaming_face_with_smiling_eyes:
As soon as he clicks on a date, the arrangement need not go to the first column. I have frozen the headings- first two rows and first three columns. So they will be visible all the time.
It is just that the available vehicles need to be highlighted so that it is easier for him to identify them readily (the human eye will immediately be drawn to highlighted cells, right).
If a customer asks for a particular honda, etc-
I have grouped the vehicles based on class- SUV, CUV, Sedan (among sedan mid size, compact). And among each class, all accords will be one below the other. All civics will be one below the other. So, u see i have grouped them. He will be able to easily trace them.
He will click on 10 october and go to the sedan section. In that sedan section, he can look for all the accords which are highlighted green.
I really hope i could insert that file or atleast pastr an image here, so that it'll be easier for u to understand the layout of the file.
Can u give me an email address sir. I will send it to u personally. You need not display ur emaul address here, just watsapp me. My watsapp is- +968 - 93 218 278
The cell to be clicked is the 4th row of each column. Eg: E4, F4,G4, ETC. Each column denotes one date. For eg: e4 is 26 september. F4 is 27 sept. G4 us 28 september.
The rows are each vehicle. B5 is land cruiser.. c5 is its number.
B6 is land cruiser. C6 its number.
B7 is prado. C7 its number
Sep 27 2022 06:35 AM
Go to my OneDrive and download "USA Lottery Numbers".
My email address is on the first worksheet...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
If you highlight booked autos in Red? - That would mean that autos in white cells are available? Why would green be an advantage?
Nothing Left to Lose
Sep 29 2022 02:45 PM
Sep 29 2022 03:51 PM
Thought maybe, that you had given up. I removed the file this morning.
In the meantime, I've created a very simple example file and put it up on OneDrive.
It is called "TestBookForAddingColors"
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
Double-click any cell in row 4 to add colors (green) - except to red colored rows.
or
Select any column cell and click the "Remove Colors" button to remove any green colors in that column.
The Green color is called "vbGreen" in VBA.
We should now have a better basis for getting what you need.
Nothing Left to Lose
Sep 29 2022 05:33 PM
Sep 30 2022 05:21 AM
Sep 30 2022 05:49 AM
Oct 01 2022 06:47 AM
I've modified your file. The file name now has an "_R1" suffix and a .xlsm extension.
VBA code is located in Module1 and in the Worksheets(Sheet2) module.
The Merge in cells B3:C4 was removed to allow use of double-click on those cells.
Row one is now visible to provide space for the 'Remove Colors' button.
Double clicking any cell in Range(B3:C4) adds a pale green color to cells directly below, if the adjacent cells in columns 5 to 101 are devoid of color.
Your worksheet (as you provided) has red cells overlaid with a conditionally formatted tan color. No changes were made to those cells or to the CF.
Download the revised file from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
Hope this does it. I've spent too much time on it.
Nothing Left to Lose
Oct 01 2022 08:00 AM