Clear Excel Cell if Value less than another Cell value

Question

I am hoping someone can help, I need to clear cells when then value of is less that a value in another cell. I did use conditional formatting but this messes up calculations further into the sheet.

I used a guide and was able to remove cells when I inputted the fixed integer into the module but am unsure how I adapt this to refer to a cell instead of a fixed number.

Thank you.

Ed

Please elaborate a bit, your problem descriptions is quiet vague — Luuklag, May 17 '18 at 13:42
Do you need to actually clear a cell, or just have the value = zero or blank? If just have it blank, why not use a formula? `=If(B2>100,"","something else")` — pgSystemTester, May 17 '18 at 13:45
Also, please attach what you currently have for attempting to solve this problem. If you've tried various methods, maybe also add a brief description of those, so you don't get repeat suggestions. — Mistella, May 17 '18 at 13:45

score 0 · Accepted Answer · answered May 17 '18 at 14:43

I believe this is what you are looking for below, this will take a value from cell B1 and compare against values in Column A, and if the values are less than the value in B1, it will clear the contents of that cell:

Sub ClearLowerThan()
    Dim c As Range, Rng As Range
    Dim LastRow As Long        
    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    'declare you worksheet, amend as required
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    'get the last row with data on Column A

    CompareVal = ws.Range("B1").Value
    'get the value to compare against, in this case from cell B1

    Set Rng = ws.Range("A1:A" & LastRow)
    'set the range to compare against the value from B1

    For Each c In Rng 'for each cell in the given range
        If c.Value < CompareVal Then c.ClearContents
        'if value of cell is less than the value to compare against, clear the cell contents.
    Next
End Sub

Clear Excel Cell if Value less than another Cell value

1 Answers1

Linked