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

Question

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:

When the value of D18 is "NA", the value of E19 to E24 is "NA".
When the value of E19 to E24 is "NC", the value of D18 is "NC".
When the value of E19 to E24 have "C" and "NA", the value of D18 is "C".
When the value of E19 to E24 have "C" and "NC", the value of D18 is "NC".
When the value of E19 to E24 have "C" and "NA" and "NC", the value of D18 is "NC".
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

Which is the meaning of the second condition (2.)? Do all cells in "E19:E24" contains "NC", or only one of them? The fifth condition is included in the fourth... — FaneDuru, Oct 27 '22 at 07:38
Hello! Still alive? If you need an answer, please answer our clarification questions. You can be helped only if you help us understand what you try accomplishing. Now, condition 3. Should the cells in the respective range have **only two strings ("C" and "NA") and all the other 4 cells to be empty**? The same for issues 4. and 5.... — FaneDuru, Oct 27 '22 at 08:53
@FaneDuru The meaning of the second condition (2) is all cells in "E19:E24" contains "NC" , Sorry for poor english — Alan Tse, Oct 27 '22 at 09:19
@Dominique I think VBA is suitable for these condition. Thank you — Alan Tse, Oct 27 '22 at 09:20
And what about my second question? "C" and "NA" should be the single values in the range? This is not a matter of the language, is a matter of logic, no offence... — FaneDuru, Oct 27 '22 at 09:21
@FaneDuru The meaning of the second condition (2) is all cells in "E19:E24" contains "NC" , Condition 3, the cells in the respective range have contains "C" or "NA" and all other 4 cells to be "C" or "NA". I will update the content and upload photo ASAP for easy reference. Sorry for my. poor English. — Alan Tse, Oct 27 '22 at 09:25
@AlanTse: you mean a UDF (userdefined function), indeed this uses VBA. But I would not use a "worksheet_change" event for this. — Dominique, Oct 27 '22 at 09:34
Did you find some time to test the adapted code I pasted 38 minutes before? If tested, didn't it return what you need? — FaneDuru, Oct 27 '22 at 10:13
@FaneDuru I tested your code, it is worked perfectly, thank you very much for your help. — Alan Tse, Oct 27 '22 at 14:38

FaneDuru · Accepted Answer · 2022-10-27T09:47:23.037

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).

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

1 Answers1