Delete a cell value/Make it blank..........

[Rajkumar]

Hi,

I need to Delete a cell value/Make it blank, if corresponding cell'
conditional formatting is met.

For ex : In column K,i am checking the duplications of cell value b
having the formulae =COUNTIF($K$7:K16,K16)>1 thru conditiona
formatting & display the same with green color.

Now i need to delete/make blank the corresponding cell value in colum
M ,if the above condition is met.

How to do it?

Thanks in advance.

Rajkuma

 

[Frank Kabel]

Hi
you need VBA for this. You may have a look at
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows
to delete duplicates and adapt this to your needs

 

[Dave Hawley]

Hi Rajkumar

I'll assume the range to watch for duplicates is K7:K100, just change to
suit.

Right click on the sheet name tab, select "View Code" and paste in
this;

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K7:K100")) Is Nothing Then
If WorksheetFunction.CountIf(Range("K7:K100"), Target) > 1 Then
Target(1, 3).ClearContents
End If
End If
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Hawley]

Rajkumar, be careful deleting rows and it often causes the dreaded #REF!
error in formulae referencing the range and named ranges. this is
particuarly true in older Excel versions. It's best (if possible) to use
Clear and then Sort. It also much faster.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****