VBA Code To Repeatedly Clear Cell Contents

[The Brown Growler]

Hi,

Would anyone please be able to help me with some VBA code to repetitively clear the contents of a cell range in an excel worksheet subject to some values in other cells of the same worksheet ?

In cell E2 I have two possible values, "In Play" or "Not In Play" (quotations marks only for illustation, not actually part of value) and in cell F2 I have one possible value, "Suspended".

In column T for cells T5 to T25 I can have 4 possible values, "PLACED", "PENDING", "CANCELLED" or a numerical value.

The worksheet is constantly refreshed from an external data source and I hope to be able to use something like an event property or similar that will react to the sheet refreshing or recalculating to initiate the VBA code.

If the cell contents in range T5 to T25 contain any value other that "PENDING" then I wish to clear the cell contents if E2 = "Not In Play" and F2 <> "Suspended".

If F2 = "Suspended" or "E2" = "In Play" I do not wish the cell contents in the range T5 to T25 to be cleared.

I think that I need some type of nested IF statement but just do not know where to start. I can open the VBA editor and paste the code into the specific worksheet but I am stuck with the code.


Thx & Rgds
Growlos

 

[ajetrumpet]

try to use the simply range code I always use, for example:

dim r as range

for each r in range("cell1", "cell2")
   if r = somevalue or r = somevalue2 then
      range("firstcell", "secondcell").select
      selection.clear
         exit for
   end if
next r

this is psuedocode, so It's not 100% correct. fool around with the IS dropdown in VB TO FIGURE OUT what the the right syntax is. you'll get it...

 

[HaHoBe]

Hi, Growlos,

Dim rngCell As Range

If [F2] <> "Suspended" And [E2] <> "In Play" Then
    For Each rngCell In Range("T5:T24")
        If UCase(rngCell) <> "PENDING" Then
            rngCell.Value = vbNullString
        End If
    Next rngCell
End If

Ciao,
Holger