Drop Down Dead !!!!!!!!

[williadb]

Hi Everyone,

I would like to select an option froma drop down box in excel. If i select this option then a ceratin range of cells say A1:A3 go red.

the drop down box was created by data validation. I would like the "open order: to turn the cells blue and "close order" to turn the cells blue. The cell colours depend on which option that you select from the drop down box. so say i selected open order and the cells were blue, as soon as i select closed order they turn red.

can anybody help?

Cheers
Dan

 

[acw]

Will the change in the drop down cause something on the sheet to recalculate???

Tony

 

[williadb]

there is an IF statement linked to the drop down box status. So cell z for example will pick up data from another cell if the drop down says "open". so in answer to your question yes.

 

[acw]

Hi

Right click on the sheet tab for the relevant sheet, select view code and put in the following code

Private Sub Worksheet_Calculate()
 If ActiveCell.Value = "Order Open" Then Range(ActiveCell, ActiveCell.Offset(0, 25)).Interior.ColorIndex = 5
 If ActiveCell.Value = "Order Closed" Then Range(ActiveCell, ActiveCell.Offset(0, 25)).Interior.ColorIndex = 3
End Sub

You may have to alter it to match the drop down codes used, and the offset number of columns.

Now when you make a change to the cell, it should color the row.

Tony

 

[murals]

Dan,

I am not sure if this is exactly what ur after. However, some modification of this code may give u ur answer. This is assuming that ur validation dropdown is on cell ref A1.

Write this code in to the worksheet code (right click on the sheet tab, view code)

Private Sub Worksheet_Change(ByVal myrange As Range)
'Dim myrange As Range

Set myrange = Range("g14:i14")
If Range("a1").Value <> "" Then
For n = 1 To 10
myrange.Interior.Color = vbGreen
Delay (0.1)
myrange.Interior.ColorIndex = xlNone
Delay (0.1)
Next n
End If

End Sub


Sub Delay(rTime As Single)
'delay rTime seconds (min=.01, max=300)
Dim oldTime As Variant
'safety net
If rTime < 0.01 Or rTime > 300 Then rTime = 1
oldTime = Timer
Do
DoEvents
Loop Until Timer - oldTime > rTime
End Sub

 

[williadb]

thats fantastic. is ther anyway that i can select at the far left and then offset from there. the drop down box is in the middle of the columns so only the cells to the right of that get highlighted.

thankyou for your time and patience with this.

Daniel

 

[acw]

Dan

Exactly what columns do you want colored? A:???


Tony

 

[williadb]

Tony,

B:Z

thanks Dan

 

[murals]

Dan,

Try replacing the range with this:

Set myrange = Range("b1":"z1")

 

[williadb]

hi i have the following. Apologies for my ignorance:

Private Sub Worksheet_Calculate()
Set myrange = Range("b1:z1")
If ActiveCell.Value = "Open" Then Range(ActiveCell, ActiveCell.Offset(0, 25)).Interior.ColorIndex = 5
If ActiveCell.Value = "Closed" Then Range(ActiveCell, ActiveCell.Offset(0, 25)).Interior.ColorIndex = 3
End Sub

its still only highlighting right of the drop down

 

[Tazguy37]

Range(ActiveCell, ActiveCell.Offset(0, 25)).Interior.ColorIndex = 5

Perhaps use the .Resize Property instead.

its still only highlighting right of the drop down

Where is the dropdown? I didn't see that information in any of the previous posts.