format color into range of cells based on specific cell input

Hi Juli,

Try the following code(untested):-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

That makes sense vcoolio- but it didn't work? I'm going to keep trying versions of it- I like this better then any I tried.
Julie

Hi Juli,

I just did a test on a mock-up of what I believe your worksheet to look like and all worked well.

Are the ranges different?

Cheerio,
vcoolio.

I can't see my own attachments for some reason? I am attaching the actual workbook (I hope). I have been entering the condition a line at a time. I have gotten to D39. If you insert 40 into D3 you will see what I am trying to do.
Juli

Hi Julie,

I'm a little confused as to what you are trying to do.

You have placed the code in the worksheet module whereas the code I supplied needs to be placed in a standard module and assigned to a button. Do you actually need a Worksheet_Change event for your purposes?

It's not, then, just cell D3 and range(D9:F14) that you are dealing with?

Cheerio,
vcoolio.

Vcoolio,
I am attaching the most up to date sheet. I used the conditional Formatting (not VBA) for the 20 minute/40 minute choice. The plan is if the 40 minute flight is chosen it blocks the extra 20 minutes out. That seems to work fine. In the same vane I would like to avoid the line by line needed for the conditional formatting by coming up with VBA code to have a drop down under aircraft assigned that when the aircraft (one of six) is selected the unavailable passenger slots will also turn grey.
I know I am not saying this clearly, but if you enter 20 or 40 in a few of the D slots I think it will make sense. I am not sure it is even possible to do what I am trying to do?

Juli

Hello Juli,

I can see that you've gone to alot of trouble with the conditional formatting. However, if you don't wish to use CF, then try the following in the worksheet module:-

Please Login or Register  to view this content.

To implement this code:-

- Clear out all conditional formatting first.
- Right click on the sheet tab then select "View Code" from the menu that appears.
- If there is any code in the large white code field, remove it.
- Paste the above code in the large white code field.

Go back to the worksheet and test in any number of the relevant "D" cells. Enter 40 in any of these cells then click away (or press enter or down arrow). The relevant range should then be greyed out.

We'll get back to the other part (drop down in aircraft assigned) once you are satisfied with this first challenge.

Please test this code in a copy of your workbook first.

Cheerio,
vcoolio.

.................or possibly this:-

Please Login or Register  to view this content.

Cheerio,
vcoolio.

vcoolio,
The first Sub didn't clear the shading if the target cell was cleared. The second Sub however worked MARVELOUSLY. I am trying to modify it so that it will work on the assigned aircraft dropdown (R) but I can't figure out where the (6, 1) and (11, 12) come from? Is that number the number of rows down?
Private Sub Worksheet_Change(ByVal Target As Range)

HTML Code: 

End Sub

I tried to use counted rows, but clearly I have it wrong. I am trying to grey out the sixth line for the first three choices and grey out the forth, fifth, and sixth line if the second three choices (aircraft) are selected.
It may also be that I have not gotten a proper grasp on the OR portion.
Juli

So...I am still trying to do this without bothering anyone but I have not gotten it down, clearly. I took the OR commands out as I thought maybe that was the problem.

HTML Code: 

try 20ish.. I took out the spaces in the Target Value - still no luck.

HTML Code: 

Just change your original code

Please Login or Register  to view this content.

Hi Juli,

The (6,1) is the offset of 6 rows down and 1 column across (from the target).
The (11,12) is the offset of 11 rows down and 12 columns across (from the target).

As for the assigned aircraft bit, I'll leave you with the following to play with as I just received a message from the wife to meet her for lunch (with the mother-in-law). I'll be back later to see how you're going so please post back whatever you have done. You're not that far away from success actually!

Refer back to your post #11:

This

Please Login or Register  to view this content.

should read as follows:-

Please Login or Register  to view this content.

These assigned aircraft numbers:-

Please Login or Register  to view this content.

are not interior color indexes.

Have fun! I'll be back.

Cheerio,
vcoolio.

Hopefully lunch was wonderful.

I changed the color. (Who knew on the original that "40" was both the number of minutes AND the color code..)

I added the 2 to the Private sub statement so that it was not the same as the previous statement.

I shortened the aircraft descriptions as I thought the spaces might be the problem.

Good News- It is not giving me any error messages.
Not so good news- It does nothing when an aircraft is selected.

HTML Code: 

Juli

Jindon,
For unknown reasons that one didn't work for me. vcoolios in post 10 worked for that portion, but I have not been able to make it work for the second sub.
Juli

Hi Juli,

With code in worksheet modules, Excel won't allow more than one code to be used but we'll get back to that later.

Will be back shortly.

Cheerio,
vcoolio.

Hi Juli,

In a standard module (I believe that Module 1 is available), place the following codes:-

Please Login or Register  to view this content.

As previously explained, in the sheet module, place the following code:-

Please Login or Register  to view this content.

We've now taken care of the problem of having two Worksheet_Change events in the sheet module.

I've attached your sample file with the codes implemented.

You'll notice that, in the sample, I've created a few drop downs with data validation which are shaded yellow. In the drop downs, I have placed the numbers 1 - 6 ( because I was too lazy to place the correct aircraft IDs in them) which you can see I've referenced in the codes above. I'll leave it to you to place the correct IDs in the drop downs and to complete the rest of them for the remainder of the data set. As your actual IDs are alpha numeric, please remember to place quotation marks around each ID otherwise you'll find that nothing happens.
Be careful where you place he drop downs as this will affect the various offsets in the codes. I suggest that you follow the pattern that I started as this will leave the offsets unaffected.

Lets hope that this is at least close to what you were hoping to achieve!

Please remember to again test these codes in a copy of your workbook.

I hope that this helps.

Cheerio,
vcoolio.