This question was aksed elsewhere and is similar to what i need to achieve. (I have modified the question in an attempt to meet the specific needs we need to solve)...
I need help to tweak the code below to perform the following task...
Here....
If value is entered in Cell A4, the user SHOULD be FORCED to enter value in either cell D1 OR G1 as well as colour cell A4 based on what is entered in EITHER cell D4 OR G4. So, for example:
In the worksheet, cell A4 contains DATE. I want to ensure that USERS are FORCED to enter Value in EITHER D4 OR G4. If a USER enter SRV in D4, I want it to show as green. if value is entered in G4, it should turn red.
Can someone help me tweak the code below to meet what i need to achieve?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim varInput As Variant
TryAgain:
If Target.Column <> 1 Then End
If Target <> "" Then
If Cells(Target.Row, "D") = "" Then
varInput = InputBox(Prompt:="Cells H" & Target.Row & " should not be blank", Title:="Warning", Default:="SRV")
If varInput = "" Then
GoTo TryAgain
Else
Cells(Target.Row, "D") = varInput
Select Case Cells(Target.Row, "D")
Case "SRV"
Cells(Target.Row, "D").Interior.ColorIndex = 1
Case "Over time"
Cells(Target.Row, "D").Interior.ColorIndex = 2
Case "Else"
Cells(Target.Row, "D").Interior.ColorIndex = 3
Case "ElseElse"
Cells(Target.Row, "D").Interior.ColorIndex = 3
End Select
End If
End If
If Cells(Target.Row, "G") = "" Then
varInput = InputBox(Prompt:="Cells K" & Target.Row & " should not be blank", Title:="Warning", Default:="SIV")
If varInput = "" Then
GoTo TryAgain
Else
Cells(Target.Row, "G") = varInput
End If
End If
End If
End Sub
It will check cells Hx & Kx as soon you change cell Ax.
With:
Select Case Cells(Target.Row, "D")
Case "Completed"
Cells(Target.Row, "D").Interior.ColorIndex = 1
Case "Over time"
Cells(Target.Row, "D").Interior.ColorIndex = 2
Case "Else"
Cells(Target.Row, "D").Interior.ColorIndex = 3
Case "ElseElse"
Cells(Target.Row, "D").Interior.ColorIndex = 3
End Select