RE: If cell value is greater than another cell value, clear conten

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

If you want to do it with 'selection' then select the range and use this

Sub sth()
For Each c In Selection
If c.Value > Sheets("lookup").Range("G4") Then
c.ClearContents
End If
Next
End Sub

Mike

"Mike H" wrote:

Hi,

Try this

Sub sth()
Dim LastRow as long
lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
For Each c In MyRange
If c.Value > Sheets("lookup").Range("G4") Then
c.ClearContents
End If
Next

Mike

"bawpie" wrote:

Okay, hopefully this is the last question today!

I've found the below formula in one of the posts here, but my brain has
completely failed on me and I'm not sure how to adapt it to my requirements.

I have a value which is entered by the user after a prompt, that populates
in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value
is 30/11/09.

I have another worksheet, 'Data', which has a column populated with dates
(say column B). I'd like a macro that looks in column B on the data sheet,
and clears out any dates which are greater than the value in cell G4. I just
want to empty those cells, not delete columns or anything else.

Sub sth()
Dim cell As Range

For Each cell In Selection
If cell.Value < 1000 Then
cell.ClearContents
End If
Next cell


End Sub

.

Relevant Pages

  • Re: Change lower case to upper case
    ... Sub Uppercase() ... ' Loop to cycle through each cell in the specified range. ... For Each x In Selection ... ' Change the text in the range to uppercase letters. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: active offset
    ... It's not clear to me what you are trying to do but this will MOVE a cell to ... Sub cutpaste() ... With Selection ... .HorizontalAlignment = xlCenter ...
    (microsoft.public.excel.programming)
  • Re: Put parentheses around figures
    ... Sub PutBracketsAroundFigures() ... Dim Cell As Range ... For Each Cell In Selection ...
    (microsoft.public.excel.programming)
  • Re: Beginner problem trying to iterate through a selection
    ... loops through the all the cells in the first column, ... exit sub ... You should be able to modify this code to check to make sure that the selection ... ' Increments cell values within a vertical single column selection ...
    (microsoft.public.excel.programming)
  • Re: Cant Undo Macro Command
    ... John's code worked on the Selection. ... Dim myRngToCopy As Range ... Dim cell As Range ... ' Specify the Undo Sub ...
    (microsoft.public.excel.programming)