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
CodePudding user response:
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).