Merge to codes under Private Sub Worksheet_Change event

[Dokat]

Hi,

I have two codes that i use to

1. Reset dependent dropdown list if value in A6 changes
2. change the cell color based on the value in the cell D6 changes.

Here are the codes i am using. Is there anyway to combine them under one code so that when values change it automatically updates.

Code to reset dependent list.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Not Intersect(Target, Target.Worksheet.Range("A6")) Is Nothing Then
        Target.Offset(0, 1).Value = ""
        Target.Offset(0, 2).Value = ""
    End If
    Application.EnableEvents = True

End Sub

Code to change cell color based on value

Sub Color_Change
Application.EnableEvents = False

Dim MyCell As Range
Dim StatValue As String
Dim StatusRange As Range

Set StatusRange = Range("D6")

For Each MyCell In StatusRange

StatValue = MyCell.Value
Select Case StatValue

    Case "Low"
    MyCell.Interior.Color = RGB(0, 176, 80)
    
    Case "Moderate"
    MyCell.Interior.Color = RGB(255, 230, 153)
    
    Case "High"
    MyCell.Interior.Color = RGB(255, 204, 204)

End Select

Next MyCell

End Sub

 

[rollis13]

Add Call Color_Change just after Application.EnableEvents = True.
Every time you change any cell in the sheet the event macro will launch your color change macro which will check cell D6.
If you need to change color only when you change A6 then insert the Call Color_Change just before the End If instead of after Application.EnableEvents = True.

 

[Dokat]

Add Call Color_Change just after Application.EnableEvents = True.
Every time you change any cell in the sheet the event macro will launch your color change macro which will check cell D6.
If you need to change color only when you change A6 then insert the Call Color_Change just before the End If instead of after Application.EnableEvents = True.

Thank you for your response. Per your recommendation i modified the code however it didnt work. Please see below updated code. It gives type mismatch error.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
   
    If Not Intersect(Target, Target.Worksheet.Range("A6")) Is Nothing Then
        Target.Offset(0, 1).Value = ""
        Target.Offset(0, 2).Value = ""
    End If
    Application.EnableEvents = True
    Call Color_Change
End Sub

Sub Color_Change()

Application.EnableEvents = False

Dim MyCell As Range
Dim StatValue As String
Dim StatusRange As Range

Set StatusRange = Range("D6")

For Each MyCell In StatusRange

StatValue = MyCell.Value
Select Case StatValue

    Case "Low"
    MyCell.Interior.Color = RGB(0, 176, 80)
   
    Case "Moderate"
    MyCell.Interior.Color = RGB(255, 230, 153)
   
    Case "High"
    MyCell.Interior.Color = RGB(226, 239, 218)

End Select

Next MyCell

End Sub

 

[rollis13]

May I ask ? is your color macro in working condition ? because the Call statement only lauches a macro (it doesn't fix non working macros ).
By the way, where does it throw the error ? which line. And please remember, I have no idea about your project.

 

[Dokat]

May I ask ? is your color macro in working condition ? because the Call statement only lauches a macro (it doesn't fix non working macros ).

Yes, it will change color based on the selection value in cell D6.

Thanks

 

[rollis13]

Before your last post I edited mine so please reply to the additional question. As said Call only launches the macro so please show the contents of cells A6 and D6 (formulas, validations or whatever).
Also, your macro has redunctant code, you could cut it down to:

Sub Color_Change()
    Select Case Range("D6").Value
        Case "Low"
            Range("D6").Interior.Color = RGB(0, 176, 80)
        Case "Moderate"
            Range("D6").Interior.Color = RGB(255, 230, 153)
        Case "High"
            Range("D6").Interior.Color = RGB(226, 239, 218)
    End Select
End Sub

 

[Dokat]

Thanks below is the formula in D6. There are no formulas in A6, It is just a dropdown list. code allows me to reset dependent drop down list when my selection in a6 changes.

Content in D6

 

[rollis13]

Sorry to say that your macro Change_Color isn't in working conditional when the item in A6 matched with B6 and C6 isn't found in your column U of list U5:Z57.
It will throw error 13 mismatch on line Case "Low".

 

[Dokat]

Sorry to say that your macro Change_Color isn't in working conditional when the item in A6 matched with B6 and C6 isn't found in your column U of list U5:Z57.
It will throw error 13 mismatch on line Case "Low".

Thanks. Is there a work aount?

 

[rollis13]

The quickest way is to add On Error Resume Next at begining of the color macro but you will have to accept a #N/D with green background in cell D6.