• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete Data

sambit

Member
Sir,
please help to resolve the below mention issue.
How to delete data which is not highlight ?

data range F3:V16

Example file attached for your reference.
 

Attachments

  • EXAMPLE - .xlsx
    12.1 KB · Views: 9
Like this?
Code:
Sub clearrng()
For Each c In ActiveSheet.Range("F3:V16")
    If c.Interior.ColorIndex = xlNone Then c.ClearContents
Next c
End Sub
See example
 

Attachments

  • EXAMPLE - .xlsm
    19.2 KB · Views: 5
Another formula solution is to mask the unwanted data with white color, so that the data will not be lost.

1] To create Range Name >>

Select F3:V16 >> Define name >>
  • Name : CellHasColor
  • Refers to : =GET.CELL(63,INDIRECT("rc",0))
  • OK
Then

2] Select F3:V16 >> Format Cell >> Font >> choose: "White" color >> OK

3] Select F3:V16 >> Conditional Formatting >> New Rule >> Use a formula... >>
  • In the Rule box, enter : =CellHasColor
  • Click "Format" >> Font >> Choose "Black" color
  • OK
4] When you fill new color to other cells, the cells will auto return the original content letter.

Remark : GetCell is a Macro 4 function, file need to be saved in xlsm type Excel Macro-Enabled Worksheet

78413
 

Attachments

  • MaskingTextColor.xlsm
    15.9 KB · Views: 1
Last edited:
Back
Top