Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts

    If a certain value is found, delete certain 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

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  4. #3
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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.

    Spreadsheet.jpg

    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

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    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.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    hajankel (2011-10-29)

  8. #6
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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.

    Spreadsheet.jpg

  9. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    hajankel (2011-10-29)

  11. #8
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

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

    kind regards

    Steve - Southampton UK

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    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.

    FYI: sdckapr is on Vacation and off line.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. The Following User Says Thank You to RetiredGeek For This Useful Post:

    hajankel (2011-10-30)

  14. #10
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

  15. #11
    New Lounger
    Join Date
    Nov 2011
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post

    auto delete content of cells

    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

    auto delete cells.xlsx

    Quote Originally Posted by hajankel View Post
    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

  16. The Following User Says Thank You to randyhut For This Useful Post:

    hajankel (2011-11-08)

  17. #12
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

  18. #13
    New Lounger
    Join Date
    Aug 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto delete content of cells

    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

  19. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  20. #15
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    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)
    '---------------------------------
    '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 = ""
            Application.EnableEvents = True
    skip:
            Next cell
        End If
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •