Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

FORCING USER TO ENTER VLAUE BASED ON OTHER CELL VALUE

0

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

Answer
Discuss

Discussion

Neither your description nor your code is complete. Moreover, there are discrepancies between your description and your code. Looking at the volume of your description and your code I prefer to deal with former. Let's try to complete it then.
You are talking of row 4. Is it true that you mean columns A, D and G in all rows? If so, starting from which row?
You say "If A4 contains DATE". What is "DATE"? A word, a date? What if A4 doesn't contain "DATE"? What if it is blank? Is anything but "DATE" a mistake?
"USERS are FORCED to enter Value in EITHER D4 OR G4". What is "Value"? Are you happy with any value at all? If they enter a question mark, does that comply with your rule?
However, you single out "SRV" which I glean from your code to be the word "SRV". Could it be "srv"?
"If a USER enter SRV in D4, I want it to show as green. if value is entered in G4, it should turn red." What is "it" in this context? I would guess A4 but that isn't what your words seem to say. Probably my mistake because your code colours D and G but your description clearly wants A coloured, not D or G.
Finally, your code has a lot of message boxes. What is the trigger for a message? I suggest that the trigger should be when the user moves to another row (it it is all rows we are talking about) without having completed the present one. That opens the question what should be done when A is blank. The apparent logic seems to be that if A4 is blank D4 and G4 may also be blank, and no colouring applied anywhere.
Variatus (rep: 4889) Mar 2, '18 at 5:17 am
Please don't use all caps in the title and put code tags around your code. Please edit your question to fix that - it makes it much easier for us to read and help you.
don (rep: 1989) Mar 2, '18 at 6:03 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login