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
Printable View
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
You can't do this with conditional formatting. It would take a macro to delete the contents. Something like this in the worksheet object:
[Or are you trying to change the text color of A1 and A2 based on the value of A3 to mask them?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
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
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.
Attachment 29550
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
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:
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
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.
Attachment 29561
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
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
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.
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
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
Attachment 29618
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
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
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
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