Delete color row

[raymondsum]

Hi,

After I compare two worksheets (VB as below) and highlight the same
value with colour, now I want to delete the same value row.

The comparison VB that I have worked:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(New,H5)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 6

How do I write VB to delete color row?

Raymond

 

[raymondsum]

Thank you for your reply.

As I am not too famililar with 'Function' of ColorOfCF, therefore, it
cant work. Is there any ways to compare two lists and return some other
indicators (not color) to me and let to delete row (eg, return 0 in
adjacent column).

Or: would you mind to elaborate more details abourt "to use a dummy
column to put in the conditional formula and then use the
autofilter to delete the rows that meet the critieria".

Regards

Raymond

 

[David McRitchie]

Hi Raymond,
Reread Tom's reply. ColorOfCF is a User Defined Function,
You would have to install it in the same manner you would
install a macro. Go back to the page you were referred to
and get the UDF. Directions to install a macros and UDFs
can be found on http://www.mvps.org/dmcritchie/excel/getstarted.htm

In order to use the function you would then have to use a WS formula
=ColorOfCF(A1) -- there may be additional parameters

But it It would be best to more directly compare the cells yourself
and delete the cells based on
a formula on your worksheet or by running an Advanced Filter
either way you need a formula. The formula you use in Advanced
Filter would be based on the active cell. If you gave more specific
information as to the condition you are testing for in a comparison
you would get a more specific answer. You can read more about
Filtering and Advanced filtering at Debra Dalgleish's
http://www.contextures.com

With a filtered list you can hide the rows you do not want to see, or
you can delete them.

Another way is to use a macro to delete duplicates, when deleting
with a macro you start the loop from the bottom of your data (not the
bottom of your sheet).

If you don't want to be involved with Advanced Filtering or macros
you might be able to use
John Walkenbach's Conditional Row Delete
http://j-walk.com/ss/excel/files/rowdel.htm