VBA to clear content in a range based on conditional formatting in a cell on the same row

[mathewwheeler]

I'm not very strong at VBA and the conditional formatting is based on a lookup value being greater than the value in column B. I would like the code to run anytime that the value in column B is updated.

So I'm creating a model and each row will be an employee - column B is the employee's start date (YR1 M1 (first day of the 5 year model) to YR5 M12 (last day of the model). I have each column (corresponding to a YR/M combination from YR1 M1 to YR5 M12) referencing a numeric value YR1 M1 = .0825, YR1 M2 = .165, YR1 M3 = .2475 ...

If the lookup value of column B is greater than the referenced numeric value, the cell is suppose to turn black (and the goal is to clear the data, so it does not flow through the model). With that detail, could you please help me with the structure of the VBA code? I am also open to changing the condition for formatting/clearing content if there you have any suggestions.

Thank you for any help! These forums are always very helpful in learning/improving.

 

[Marcelo Branco]

Maybe something like this

Paste this code in the worksheet Code Page

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rStDate As Range, vYear As String, vMonth As String, dbValDate As Double
    Dim rCell As Range
    
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    'Assumes data in rows 6:100. Adjust to suit
    Set rStDate = Range("B6:B100")
    
    If Not Intersect(Target, rStDate) Is Nothing Then
        vYear = Mid(Target, 3, 1)
        vMonth = Mid(Target, InStr(Target, "M") + 1)
        dbValDate = (12 * (vYear - 1) + vMonth) * 0.0825
        
        Application.EnableEvents = False
        For Each rCell In Range("C5:Z5")
            If dbValDate > rCell.Value Then Cells(Target.Row, rCell.Column).ClearContents
        Next rCell
        Application.EnableEvents = True
    End If
        
End Sub

M.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...elete-values-cell-based-cell-color-black.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).