Need Help with the VBA Coding ... not even sure if it can be done by simple formula a

I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


Can somebody please help me with this??


I sincerely appreciate all your help and Thanks a ton in advance.

** Find the sample sheet attached.

muin said:

I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


Can somebody please help me with this??


I sincerely appreciate all your help and Thanks a ton in advance.

** Find the sample sheet attached.

Click to expand...

Can someone help me with this please... Its a bit urgent.

Thank You for the support.

Hi muin,

The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)


For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

HTH,

Hi Ken,

First of all thanks very much for the prompt reply, i tried using your code and also used ur signature link to see how it needs to be done. I tried but it didnt work !! Perhaps i am doing something wrong?. Also i need this for the whole sheet not just one cell. If you can give me your email, i shall attach my work, so that you can go through it and come up with a solution. Its important and urgent as well.

Waiting for your reply.

Ken Puls said:

Hi muin,

The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim sVal As String


    Select Case Sh.Name
        Case Is = "Sales", "Production", "logistics"
            If Target.Address = "$B$3" Then
                Application.EnableEvents = False
                
                sVal = Sh.Range("B3").Value
                Worksheets("Sales").Range("B3").Value = sVal
                Worksheets("Production").Range("B3").Value = sVal
                Worksheets("logistics").Range("B3").Value = sVal
                
                Application.EnableEvents = True
            End If
        Case Else
            'Do nothing
    End Select


End Sub

For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

HTH,

Click to expand...

First off, my apologies, as that link isn't as clear for this kind of code. This code needs to go in the ThisWorkbook module, not a standard code module. So look for the ThisWorkbook object, put the code there, then remove the standard module you created.

Now... for the whole sheet... if I give you code to do that, we're essentially creating 3 exact copies of the same worksheet. In addition to being complete duplication, it will be HORRENDOUSLY slow to do, so why?

(There is an option to upload a workbook if you click "Go Advanced" for your reply.)

Not Sure if Im in the right thread. Im looking for a code to run on a column.

e.g
Cells in Column D = Low, Medium, or High
Cells in Column E = Low, Medium, or High

Column F should have the following conditions:


1. Green: LL(Low Probability, Low Impact), LM (Low Probability, Medium Impact), ML (Medium Probability, Low Impact)

2. Yellow: LH (Low Probability, High Impact), MM (Medium Probability, Medium Impact), HL (High Probability, Low Impact)

3. Red: MH (Medium Probability, High Impact), HM (High Probability, Medium Impact), HH (High Probability, High Impact).


Please help.

Thanks