excelvbacellintersect

Excel VBA: Change cell value based on other cell value changed

I am writing the VBA that about change the cell (E19:E24) value if cell D18 value changed and change the cell D18 value if E19: E24 all or anyone cell value changed.

I want to fulfil below scenario:

  1. When the value of D18 is "NA", the value of E19 to E24 is "NA".
  2. When the value of E19 to E24 is "NC", the value of D18 is "NC".
  3. When the value of E19 to E24 have "C" and "NA", the value of D18 is "C". enter image description here
  4. When the value of E19 to E24 have "C" and "NC", the value of D18 is "NC". enter image description here
  5. When the value of E19 to E24 have "C" and "NA" and "NC", the value of D18 is "NC". enter image description here
  6. When all the value of E19 to E24 is "C" or "NA", the value of D18 will be "C" or "NA".

Now that I have done the scenario 1 and 2, but i don't know how to write VBA for scenario 3-6.

Would anyone help me? Thank you very much.

Below is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Intersect(Target, Range("D18")) Is Nothing Then
If Not Intersect(Target, Range("E19:E24")) Is Nothing Then
    If Target.Value = "NC" Then Range("D18").Value = "NC"
End If
Else
    'Target.Value = D18 Value
    Select Case Target.Value
        Case "NA"
            Range("E19:E24").Value = "NA"
    End Select
End If
Application.EnableEvents = True
Solution

  • Please, try the next adapted code:

    Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rngE As Range: Set rngE = Range("E19:E24")
 Dim rngD As Range: Set rngD = Range("D18")
 
 If Target.address = rngD.address Then
    Application.EnableEvents = False
      If Range("D18").Value = "NA" Then Range("E19:E24").Value = "NA" '1
    Application.EnableEvents = True
 ElseIf Not Intersect(Target, rngE) Is Nothing Then
    Dim countC As Long, countNA As Long, countNC As Long
    countC = Application.CountIf(rngE, "C")
    countNA = Application.CountIf(rngE, "NA")
    countNC = Application.CountIf(rngE, "NC")
    Application.EnableEvents = False
    If countNC = rngE.cells.count Then
        rngD.Value = "NC"      '2
    ElseIf countC > 0 And countNA > 0 And (countC + countNA = rngE.cells.count) And countNC = 0 Then
        rngD.Value = "C"       '3
    ElseIf countC > 0 And countNC > 0 And ((countNA = 0 And countC + countNC = rngE.cells.count) Or _
                                                    (countNA > 0 And countC + countNC + countNA = rngE.cells.count)) Then
        rngD.Value = "NC"     '4 - 5
    ElseIf countC = rngE.cells.count Then
        rngD.Value = "C"      '6 (1)
    ElseIf countNA = rngE.cells.count Then
        rngD.Value = "NA"     '6 (2)
    End If
    Application.EnableEvents = True
 End If
End Sub

    The above code is based on the next assumptions:

    a. Conditions 1, 6 (1 and 2) are met if all the range strings are the same ("C" or "NA" or "NC").

    b. Condition 3 is met if there is minimum a cell containing "C", minimum a cell containing "NA" and all the rest contain one of the two mentioned strings

    c. Conditions 4 - 5 are met if minimum a cell has its value as "C", minimum a cell has its value as "NC" and (if no "NA" cell, all the range cells are filled with the two mentioned strings, but in case of minimum one cell containing, also, "NA" all the range cells must contain values of all three cases).