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

  • Thread starter Thread starter Rajkumar
  • Start date Start date
R

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
 
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 *****
 
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 *****
 
Back
Top