Clear Cell Value if Another Cell is not blank

[hmltnangel]

Hi all,

I have tried many little bits and pieces to try and make this work, but im guessing im just not getting the syntax right. So if anyone can point me to the answer, and how I went wrong - itd be appreciated.

I have a nice piece of code that someone on here helped with - this works great, so I dont want to mess it too much.

Effectively after the first step, I want to add a small piece that changes the contents of AB to 0, if AC is not blank, and also changes the value of AB to 0.5 if D equals "Red" AND the value input in AB is less than 0.5.

The code is below so far and where I would like to add that piece is marked

Private Sub Worksheet_Change(ByVal Target As Range) 'WS change is whenever a value in target cell is changed

Dim cell As Range
Dim msg As String


' Colour Checker - MsgBox Target.DisplayFormat.Interior.Color -

    If Intersect(Target, Range("AC4:AC2000")) Is Nothing Then Exit Sub

    If cells(Target.Row, 30).Value <> "" Then
        msg = msg & vbCrLf & vbCrLf & "Ineligible for Merit Award: " & cells(Target.Row, 30).Value
    End If
    
    If msg <> "" Then MsgBox msg, Title:="Ineligible Colleague"
    
'POP THIS NEW BIT OF CODE IN HERE

    
If Target.Value > 0 Then
            If Target.DisplayFormat.Interior.Color = 8696052 Then
            msg = "High Comp Ratio: Individuals Comp Ratio is in excess of 120%. Recommendation is that Merit Award does not exceed 1%"
            End If
End If

If Target.Value > 0 Then
            If Target.DisplayFormat.Interior.Color = 13551615 Then
            msg = vbCrLf & vbCrLf & "Low Comp Ratio: Individuals Comp Ratio is below 80%. Consideration should be given to providing a higher increase for this individual"
            End If
End If


'   Check value in column 22
    If cells(Target.Row, 22).Value = "0 - Too new to rate" Then
        msg = msg & vbCrLf & vbCrLf & "Too New To Rate: This employee has not had a formal Performance Appraisal for FY23. Please ensure that the performance is still factored into your merit decision."
    End If

'   See if any message to return
    If msg <> "" Then MsgBox msg, Title:="Potential Issues to Address"

End Sub

 

[rlv01]

Effectively after the first step, I want to add a small piece that changes the contents of AB to 0, if AC is not blank, and also changes the value of AB to 0.5 if D equals "Red" AND the value input in AB is less than 0.5.

This seems like a conflict. You have two conditions you want to address

Condition #1: AC is NOT blank

Condition #2: D = "Red" AND AB < 0.5

And you imply that Condition #2 should also be evaluated when Condition #1 is true.

But if code is added to set AB to 0 for Condition #1, it impacts Condition #2 since AB is always going to be < 0.5 so you might as well shorten Condition #2 to D = Red

Is that what you intended?

 

[hmltnangel]

Thanks,

You are kind of right that it seems a slight contradiction. In its simplest of terms this is how the logic should be.

If AC is not blank and D Equals "Red", then change AB to 0.5
If AC is blank and D equals "Red", then if user input value of AB is <0.5, change AB to 0.5

 

[rlv01]

How does this

If AC is not blank and D Equals "Red", then change AB to 0.5

coexist with your other requirement?

If AC is not blank, then change AB to 0​

 

[hmltnangel]

Thanks RLv01

If AC is not blank (it will contain something such as ineligible for whatever reason has been selected) and D equals the text "Red" (an indicator of a status) then I would like the value in AB to automatically be 0.5.

Alternatively.

If AC is blank and D equals "Red", then the minimum value should be 0.5.

I think I wasnt clear enough in my initial question. But these are the specific requirements now.

 

[rlv01]

Your requirements still appear to conflict to me, but perhaps something like this.

    'POP THIS NEW BIT OF CODE IN HERE
    
    Dim D, AC, AB As Range
    
    D = Me.Cells(Target.Row, "D").Value
    AC = Trim(Target.Value)
    Set AB = Target.Offset(0, -1)
    
    If D = "Red" Then
        If AC = "" Then
            AB.Value = 0.5
        Else
            If AB < 0.5 Then
                AB.Value = 0.5
            End If
        End If
    Else
        If AC <> "" Then
            AB.Value = 0
        End If
    End If

 

[hmltnangel]

Thanks RLV01. Its close but not quite. If I add this snippet in (note column reference are all +1 due to additional data column being added) I get two issues noted below.

1- This puts the value that should be in AC, into AB
2- This puts 0 in every row as there is a formula in AD. The formula result is blank but as the cell is not "empty" it inputs a value

    Dim E, AD, AC As Range
   
    E = Me.cells(Target.Row, "E").Value
    AD = Trim(Target.Value)
    Set AC = Target.Offset(0, -1) NOTE - PROBLEM 1
   
    If E = "Red" Then
        If AD = "" Then
            AC.Value = 0.005
        Else
            If AC < 0.5 Then
                AC.Value = 0.005
            End If
        End If
    Else
        If AD <> "" Then NOTE - PROBLEM 2
            AC.Value = 0
        End If
    End If

As a side note - did try this instead, and like the snippet above, it kind of works, but not exactly. Where this goes wrong is that it seems to ignore the check for "Red" and "not blank", and inputs the value 0.5 in every row regardless of the conditions,

        Application.EnableEvents = False
   If Intersect(Target, Range("AC4:AC2000")) Is Nothing Then Exit Sub
   If cells(Target.Row, 5).Value = "Red" & cells(Target.Row, 30).Value <> "" Then
    Target.Value = ("0.5%")
    End If
        Application.EnableEvents = True

Could either of these be tweaked to work correctly?

 

[rlv01]

Thanks RLV01. Its close but not quite. If I add this snippet in (note column reference are all +1 due to additional data column being added) I get two issues noted below.

1- This puts the value that should be in AC, into AB
2- This puts 0 in every row as there is a formula in AD. The formula result is blank but as the cell is not "empty" it inputs a value

Well it is none too suprising that code which is based on specific column references won't work the same if you add or delete columns. Also with respect to " This puts the value that should be in AC, into AB", that's not going to workout well since you are still using this line of code If Intersect(Target, Range("AC4:AC2000")) Is Nothing Then Exit Sub. Honestly, I'm losing interest in following the twists and turns.