Drop down in one cell & fill another cell

[MontySharpei]

Hello,

Is there code that can do the following :

say sheet 1 cell A1 contains a simple drop down list of 'yes and no'.

if 'yes' is selected, then cell B1 will remain blank
if 'no' is selected, then cell B1 will fill black.

at the same time
if the 'yes' selected in sheet 1 cell A1 the wholw row A is copied into sheet 2 ??

Thanks in advance..

[Bob Phillips]

[vba]


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If LCase(.Value) = "yes" Then

.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
.EntireRow.Copy Me.Parent.Worksheets("Sheet2").Range("A1")
Else

.Offset(0, 1).Interior.Color = vbBlack
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[MontySharpei]

thanks xld, but now to progress this is it possible that the

Const WS_RANGE As String = "A1" can be adapted to a moving cell?

inother words it first being a1, then the next time a2, then the next time a3 etc etc....

(if this makes sense ??)

[Bob Phillips]

You could put the row number in a cell, pick that up in the code, and increment it as well.

[MontySharpei]

In the attched file when the command button is pressed in the sales request sheet a userform is completed by the user.

This populates some of the cells in the sheet. In doing so it puts a drop down menu in column G on the lines completed.

On the first entry this is in cell G5., on the second entry it will be G6, etc..

If the user now selects 'Trace' in G5 from this dropdown i would like the cell next to it to remain clear and for the line to be copied across to the sheet 'Audit review' on the next available line.

However should the cell be selected with no trace i would like the cell to fill black, and not to copy across.

How does the code need to be ammeded to make this possible ????

[MontySharpei]

Ok, from the new attached file I have now sorted the bit out where it fills the cell when "trace" is selected. The code also copies it across to the second sheet (audit review) when trace is selected.

However the problem i have is that the next time another line is added on the first sheet (Request), it overwites the previous line, instead of creating the begining of a list.

How does the code need amending to stop this ??

[MontySharpei]

Once more pressed the button before adding the file !!!

Ok, from the new attached file I have now sorted the bit out where it fills the cell when "trace" is selected. The code also copies it across to the second sheet (audit review) when trace is selected.

However the problem i have is that the next time another line is added on the first sheet (Request), it overwites the previous line, instead of creating the begining of a list.

How does the code need amending to stop this ??

thus the audit page showing a list of of those lines that were "traced" ???

[phdwsm]

my need is very similer i have a spreadsheet with a column in it which contains a score 1-4, if 1 or 2 is entered the cell turns red if 3 is entered the cell turns orange. i would like to copy any row from worksheet 1 to worksheet 2 for any cell colours red or orange.

[Bob Phillips]

Use a worksheet change event to check that column for 1, 2 or 3 and copy it when so.