Changing Values of one cell based on the Other in Excel

[aakash_sehgal]

I am new to Excel and VBA. I am making a Project list wherein I have two columns for Status, Status and Status in Words. In the former I Need to enter the Status in form of numbers, e.g. 50% or 80% and in the latter I Need to enter the Project Status in words i.e. Open, Closed, In Progress etc. My Problem is that when I write 50 % in the first column, the Status in the other column should automatically Change to In Progress and vice versa if I type Closed in the second column, then the Status in the first column should Change to 100%. Could anyone tell me how to procede with this in Excel vba

 

[Fishboy]

I am new to Excel and VBA. I am making a Project list wherein I have two columns for Status, Status and Status in Words. In the former I Need to enter the Status in form of numbers, e.g. 50% or 80% and in the latter I Need to enter the Project Status in words i.e. Open, Closed, In Progress etc. My Problem is that when I write 50 % in the first column, the Status in the other column should automatically Change to In Progress and vice versa if I type Closed in the second column, then the Status in the first column should Change to 100%. Could anyone tell me how to procede with this in Excel vba

Hi aakash_sehgal, welcome to the boards.

Without know which columns you are referring to I have had to assume you mean columns A and B. The following Worksheet_Change macro will trigger whenever cells in columns A or B are manually updated. To try it out first make a COPY of your workbook to test in. In the COPY of your workbook right-click on the tab name of the sheet you want this to work on and then select View Code. In the new window that opens simply copy / paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' If target column is column A then...
If Target.Column = 1 Then
    ' If the target value is greater than 0 but equal or lower than 99 then...
    If Target.Value > 0 And Target.Value <= 99 Then
        ' Disable events to prevent infinite loop issues
        Application.EnableEvents = False
            ' Update the adjacent cell in column B to In Progress
            Target.Offset(0, 1).Value = "In Progress"
        ' Re-enable events
        Application.EnableEvents = True
    ' Else if the target value is either blank or 0 then...
    ElseIf Target.Value = "" Or Target.Value = 0 Then
        ' Disable events to prevent infinite loop issues
        Application.EnableEvents = False
            ' Update the adjacent cell in column B to Open
            Target.Offset(0, 1).Value = "Open"
        ' Re-enable events
        Application.EnableEvents = True
    ' Else if the target value is greate than 99 then...
    ElseIf Target.Value > 99 Then
        ' Disable events to prevent infinite loop issues
        Application.EnableEvents = False
            ' Update the adjacent cell in column B to Closed
            Target.Offset(0, 1).Value = "Closed"
        ' Re-enable events
        Application.EnableEvents = True
    End If
End If
' If the target column is column B then...
If Target.Column = 2 Then
    ' If the new target value is Closed then...
    If Target.Value = "Closed" Then
        ' Disable events to prevent infinite loop issues
        Application.EnableEvents = False
            ' Update the adjacent cell in column A to 100
            Target.Offset(0, -1).Value = 100
        ' Re-enable events
        Application.EnableEvents = True
    End If
End If
End Sub

Remember to save the document as a macro enabled workbook (.xlsm format)

 

[mumps]

You didn't specify which columns you were using so I assumed that your Status is in column A and your Status in Words is in column B. You can change the code to suit your needs. Also, you will have to format the column containing the status to 'Percentage'. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the status in column A and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,B:B")) Is Nothing Then Exit Sub
    If Target.Column = 1 And Target < 1 Then
        Cells(Target.Row, "B") = "In Progress"
    ElseIf Target.Column = 2 And Target = "Closed" Then
        Cells(Target.Row, "A") = 1
    End If
End Sub

 

[aakash_sehgal]

You didn't specify which columns you were using so I assumed that your Status is in column A and your Status in Words is in column B. You can change the code to suit your needs. Also, you will have to format the column containing the status to 'Percentage'. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the status in column A and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Intersect(Target, Range("A:A,B:B")) Is Nothing Then Exit Sub
    If Target.Column = 1 And Target < 1 Then
        Cells(Target.Row, "B") = "In Progress"
    ElseIf Target.Column = 2 And Target = "Closed" Then
        Cells(Target.Row, "A") = 1
    End If
End Sub

Hello, Thankyou for your valuable Responses.
I tried the code and it works good but I have a Problem that after implementation, Excel crashes. Could you please guide me a more on this. The error is "method 'range of object' _Worksheet failed". Here is my code attached. I also have some other code in the same worksheet. If you want I can update that also. I do not understand the use of Application.EnableEvents...Maybe that is a Problem.

'This is a global variable, prevents endless loops in worksheet change.
Dim running As Boolean


'When any change is made on the worksheet, sends target as the changed cell
'Private Sub Status_Change(ByVal Target As Range)

'Checks to see if a worksheet change is already running, if it is, then it quits
If running = True Then Exit Sub
'We are running code now, so prevent endless loops
running = True
Dim Status, StatusInWords As Integer

'Set to your values
Status = 14
StatusInWords = 15

' If this is a column you want to change
If Target.Column = StatusInWords Then

'Change values
If Target.Value = "offen" Then
Target.Offset(0, -Abs(Status - StatusInWords)).Value = "0%"

ElseIf Target.Value = "In Arbeit" Then
Target.Offset(0, -Abs(Status - StatusInWords)).Value = "50%"

ElseIf Target.Value = "vorlüufig abgeschlossen" Then
Target.Offset(0, -Abs(Status - StatusInWords)).Value = "80%"

ElseIf Target.Value = "abgeschlossen ohne ergebniseintrag" Then
Target.Offset(0, -Abs(Status - StatusInWords)).Value = "90%"

ElseIf Target.Value = "Closed" Then
Target.Offset(0, -Abs(Status - StatusInWords)).Value = "100%"
End If

'If this is the other column you want to change
ElseIf Target.Column = Status Then

'Change values
If Target.Value = 0 Then
Target.Offset(0, Abs(Status - StatusInWords)).Value = "offen"

ElseIf Target.Value = 0.5 Then
Target.Offset(0, Abs(Status - StatusInWords)).Value = "In Arbeit"

ElseIf Target.Value = 0.8 Then
Target.Offset(0, Abs(Status - StatusInWords)).Value = "vorlüufig abgeschlossen"

ElseIf Target.Value = 0.9 Then
Target.Offset(0, Abs(Status - StatusInWords)).Value = "abgeschlossen ohne ergebniseintrag"

ElseIf Target.Value = 1 Then
Target.Offset(0, Abs(Status - StatusInWords)).Value = "Closed"


End If
End If
running = False

 

[aakash_sehgal]

Hey I figured out the Problem . It was because of Application.EnableEvent = False