Can a dropdown list option add color/time to a range?

[Guest]

I am trying to build a truck dispatching spreadsheet. If I have a dropdown
list of timed truck runs, is it possible, after selecting one, to have it
color a range across columns that represent the amount of time blocked for
that particular load?
For example, if one option was "Load from Atlanta to Charleston (6hrs)", is
there a way to have it color a range of six cells? (Each column represents an
hour. There are 12 across from 0600 to 1800.)
How do you associate a time to a dropdown option and a color to an option?
Or, what is the best way to do this?
Thanks in advance.

 

[L. Howard Kittle]

Hi Outbacker,

Try this to maybe get you going, where the drop down is in B1 and the hours
start in column D1. If you are going to have a bunch of trucks drive time
rows colored, the "Cells.Interior.ColorIndex = xlNone" will have to be
modified because this wipes out all color on the sheet. It may be possible
to do this with conditional formatting. I will give it a go with CF but
can't do it right now.

Copy into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("B1") Then Exit Sub
Dim i As Integer
i = Range("B1").Value
Cells.Interior.ColorIndex = xlNone
Range("D1").Resize(1, i).Interior.ColorIndex = 6
End Sub

HTH
Regards,
Howard