If a certain value is found, delete certain cells

Topic

New Reply

Hi again chaps

I would like to use conditional formatting to do the following. If I find the word YES in a cell, ie: A3, then delete the contents of A1 and A2…

any help on this one please.

kind regards

Steve – Southampton UK

Excel 2010

Reply | Quote

Replies

You can’t do this with conditional formatting. It would take a macro to delete the contents. Something like this in the worksheet object:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    If UCase(Range("A1")) = "YES" Then
      Application.EnableEvents = False
      Range("A2").ClearContents
      Range("A3").ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

[Or are you trying to change the text color of A1 and A2 based on the value of A3 to mask them?
If so, use the formula
=$A$3=”Yes”

for the conditional formatting of A1 and A2 and set the format to match the background color of the cells. [Unless the background is black or white, this frequently will not mask the text in printing…]

Steve

Reply | Quote

Hi Steve, thanks very much indeed for the rapid response, I have inserted this picture so you can see what I am trying to do.

29263-Spreadsheet

I really do not now which one to use, how do I insert this macro? Perhaps there may be an easier way?

Once again, thanks very much for having a look at this for me.

Steve – Southampton UK

Reply | Quote

Steve,

What cell is the YES in? Will it always be only this row?
The final code will be pasted into the ThisWorkbook section (yellow highlight) where the Red circle is. This screen is reached by pressing Ctrl+F11 while in Excel. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

In the code change the “A1” to the cell you want the code to react to.
Change the A2 and A3 to the appropriate ranges, add another line with the appropriate range.

RetiredGeek indicates where the code should be located.

But are you really looking to delete many cells by changing multiple cells and not just 1? You need to be explicit about this…

Steve

Reply | Quote

Hi again gentlemen,

okay, the exact cells in question are ALWAYS, B9 (this is where the YES is) and the deletion OR blanking is the 3 cells above it, however this is required to work on the right, literally as far right as I require it to go. See attached basic illustration.

29273-Spreadsheet

Reply | Quote

Try this code. It checks to see that the column is > 1 (col A=1) and that the row is one of every 7th row starting at 9 (Rows 9, 16, 23, 30, etc). If one of those cells changes, and the value is yes (the ucase ensures the case is irrelevant) it clears the 3 rows above it in the column.

Steve

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCell As Range
  Dim lRow As Long
  Dim iCol As Integer
  Dim x As Long
  For Each rCell In Target
    iCol = rCell.Column
    lRow = rCell.Row
    If iCol > 1 And (lRow - 9) Mod 7 = 0 Then
      If UCase(Cells(lRow, iCol)) = "YES" Then
        Application.EnableEvents = False
        Range(Cells(lRow - 1, iCol), Cells(lRow - 3, iCol)).ClearContents
        Application.EnableEvents = True
      End If
    End If
  Next
End Sub

Reply | Quote

Hi again Steve,
I was wondering how to insert this code?

I have opened the vba editor [alt=f11], and inserted your code, when I try to run it, it asks for a macro name, what name do I give it? Unfortunately I am not an expert on the coding side of things :mellow:

Any further help please Steve, then I can find out whether it works 🙂

kind regards

Steve – Southampton UK

Reply | Quote

Steve,

The code written by Steve (sdckapr) is self executing when a cell is changed, this is known as event driven code, e.g. the event of a cell being changed triggers the code to run. It is not designed to be executed manually. Just change the value in one of the YES/NO cells and you’ll see the changes. :cheers:

FYI: sdckapr is on Vacation and off line.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Steve

hope you have a nice vacation, thanks very much for all your help, I will mess around with this when I get to work in the morning.

kind regards

Steve – Southampton UK

Reply | Quote

Hello –

Set up your spreadsheet with five columns. Column “A” and column “B” contains your input data.
Column “C” is your condition state: =IF(B2>A2,”YES”,”NO”) [displays “YES” if the value of column “A” is less than “B”]
The formula for column “D” is: =IF(C2=”yes”,””,A2)
The formula for column “E” is: =IF(C2=”yes”,””,B2)

So, your input is in columns “A” and “B”, and your results based upon the condition in “C” are in columns “D” and “E”.

Hope this helps. See attached.

randyhut

29327-auto-delete-cells

Hi again chaps

I would like to use conditional formatting to do the following. If I find the word YES in a cell, ie: A3, then delete the contents of A1 and A2…

any help on this one please.

kind regards

Steve – Southampton UK

Excel 2010

Reply | Quote

Hi Randy,

thanks very much for you advice, I will try this method today, and get back to you with the result..

kind regards

Steve – Southampton UK

Reply | Quote

My issue is similar but with a twist. if i have a range of cells A1 – A10, one of which will contain the number ‘1’ , what code do i need to write so that if another ‘1’ is put elsewhere in the range, it automatically removes the existing ‘1’, i.e only allowing one ‘1’ in the range at a time.

Thanks

Bob

Reply | Quote

I am not sure I understand completely. Could you elaborate? Is it ONLY the number 1 or is it more general. If a duplicate is found, what should happen to the duplicate cell: clear it, change it to a different number or what.

If the number is entered with a copy of a range (ie multiple cells) and there is duplicates within this range, which one has precedence?

Steve

Reply | Quote

Bobby,

Place the following code the worksheet module. This example is set up for a range of cells A1 through A10. If a 1 is entered in that range, it checks for the presence of another 1 and removes the original. It ignores all other values in the range and any values outside the range. Change Range(“A1:A10”) to the range you want and the 1 to any number or “string” you please. The routine is automatic so there is nothing that you will need to do to initiate the code.

HTH,
Maud

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=”#008000″]’———————————
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim rng As Range
    Dim cell As Range
    Set rng = Range(“A1:A10″)
[COLOR=”#008000”]’———————————
‘CHECK RANGE AND REMOVE OLD MATCHING VALUE[/COLOR]
    If Not Intersect(Target, rng) Is Nothing And Target = 1 Then
        For Each cell In rng
        Application.EnableEvents = False
            If cell.Address = Target.Address Then GoTo skip
            If cell = Target Then cell = “”
        Application.EnableEvents = True
skip:
        Next cell
    End If
End Sub

Reply | Quote

Maud & Bobby,

A slight modification of Maud’s fine code.
As written it will leave Application Events Turned OFF if you change A10 to 1 and thus will no longer operate and will also disable any other event driven code.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'---------------------------------
'DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A1:A10")
'---------------------------------
'CHECK RANGE AND REMOVE OLD MATCHING VALUE
    If Not Intersect(Target, rng) Is Nothing And Target = 1 Then
        Application.EnableEvents = False
        For Each cell In rng
            If cell.Address = Target.Address Then GoTo skip
            If cell = Target Then cell = ""
skip:
        Next cell

        Application.EnableEvents = True

    End If

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

oversight…good pickup but I would rather put it here.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
‘———————————
‘DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim cell As Range
    Set rng = Range(“A1:A10”)
‘———————————
‘CHECK RANGE AND REMOVE OLD MATCHING VALUE
    If Not Intersect(Target, rng) Is Nothing And Target = 1 Then
        For Each cell In rng
        Application.EnableEvents = False
            If cell.Address = Target.Address Then GoTo skip
            If cell = Target Then cell = “”
skip:
        Application.EnableEvents = True
        Next cell
    End If
End Sub

Reply | Quote

Maud,

I think that is a little inefficient as you are doing a turn on/off for each iteration through the loop rather than only once for the whole loop. There is no need to have events on while going through that loop so why bother with the on/off? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

…because if placed at the end and the code is interrupted mid loop, the likelihood of disabling the events becomes probable and the astute point you initially made become moot. Since interrupts are not disabled, one scenario would be if the user presses crtl-break. However, I do understand what you are driving at.

Reply | Quote

Maud,

Good point! Although it would be hard to break in a 10 cell loop…really fast fingers. 😆

BTW: Application.EnableEvents is not persistent, i.e., restarting Excel sets it back to True.

This is exactly why I have a macros in my Personal.xls file that fix common problems (such as Events being turned off) at the touch of a key sequence. They come in real handy especially when debugging new code. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

For a 10 loop procedure, yes you would need really fast fingers. But is it really clear that the working copy may not be 1000 cells in the range? And who is to say that additional code may not be appended prior to the line of code in this routine in the future that is error prone or perhaps branches from a call to a second routine. I think it is always better to be safe than sorry.

Reply | Quote