Vehicle Management help required

[ASHWIN670]

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 

[#2]

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 Lose

[#3]

@Nothing_Left_to_Lose 

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.

[#4]

@ASHWIN670 

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)

[#5]

@Nothing_Left_to_Lose 

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

[#6]

@ASHWIN670

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

[#7]

apologies for the delay in response Sir.

I could not find a file called USA Lottery Numbers among the files.

I would not be highlighting the vehicles in RED. it will be like this.

let us say- a honda civic is in cell B24.

It needs to be booked from the (1st - 10th Oct). 1st Oct is in cell K4. 2nd Oct is in cell L4. 3rd Oct is in M4.... so on and so forth till 10th Oct is in T4.

The cells i will highlight in red will be k24 to T24 (all the cells in between will be highlighted red).

So, when i click on any date between 1st and 10th oct, this particular civic should not be showing green.

[#8]

@ASHWIN670 

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

[#9]

sir
pls find my file in the below link in a folder called "documents"

thank you for your time and effort

in the file you have sent, yes, you are close. but

1) the green highlight should happen on the vehicle. in columns B and C

2) the red colour does not extend to the entire row. it will be only for the dates tha vehicle is booked for

3) that green highlight is not disappearing once it is highlighted. (pls see your file. i have shared that file as well in my documents.)

once you see my file, things might be clearer

[#10]

It is 5:19 am - Friday - and there is no link in your message

[#11]

oh Sorry Sir. Here it is

https://1drv.ms/u/s!AgH2Q6Ehi4tFaO_wrMRRUr4xMAs?e=JAjjdr

[#12]

@ASHWIN670 

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

[#13]

Thank you so much sir. Thank you so much for your time and effort. I understand, experimenting with something like this takes a lot.

But this is not how i wanted it. No problem, i don't want you spending any more time on this. But just for understanding, i will explain from the beginning

Now that you have the file, you will be able to understand easily.

When a customer calls, they will ask for a particular date. be it any vehicle.

So, we must see- what are the vehicles on that particular date.

When we CLICK ON THAT DATE, all the vehicles NOT ALREADY BOOKED for THAT DATE should get highlighted IN THE COLUMN 'B' & 'C'.

Here, the click input should be on the DATE. Just exactly like what you had done earlier in your file "TEXT BOOK FOR ADDING COLORS". But the highlight should happen on the columns 'B' & 'C'

Each date will have different vehicles available. so it can't be a common click on the "VEHICLE" Heading or "Number" Heading.

well, that was a great experience getting associated with you sir. Thank you so much for your time and effort. well appreciated