Data validation in another way.

[danish6061]

I have an Excel sheet that has a list of problems, the resolution to those problems, and the status of completion.
Column G is where the users are to enter the needed resolution to the problem. Column H is where the users indicate the status (with only three options):
* Not started
* In Progress
* Complete

Users should always enter text into column G that explains what the resolution to the problem is. Then they should change the status to indicate it is now either "In Progress" or "Complete".
I have conditional formatting set so the cell in column G is highlighted in bright yellow until something is entered into it, but users are still sometimes changing the status without providing a resolution which I'd like to prevent.

Is there a way to prevent the user from changing the status to "In Progress" or "Complete" until something has been entered in the resolution column. So if they try to change the status without something being entered in Column G (Resolution column), they would receive an error message letting them know they must first enter a resolution before they can change the status.

Is there a way to do that in Excel?
Thanks!

 

[DanteAmor]

It could be with VBA, put the following code in the sheet events:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim c As Range
  If Not Intersect(Target, Range("H:H")) Is Nothing Then
    For Each c In Target
      If Not Intersect(c, Range("H:H")) Is Nothing Then
        If Cells(c.Row, "G") = "" Then
          MsgBox "First enter a resolution before change the status"
          Cells(c.Row, "G").Select
          Exit Sub
        End If
      End If
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[danish6061]

Thank you so much DanteAmor!
Could you tell me how can I do this same in google sheet??

 

[DanteAmor]

Sorry, I don't have google sheet, I can't test. Maybe someone else can help.

 

[danish6061]

Thanxx DanteAmor for your support.