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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

You can't programmatically check for a color produced with conditional
formatting. You have to check the underlying condition. It would be easier
to use a dummy column to put in the conditional formula and then use the
autofilter to delete the rows that meet the critieria.

Otherwise, you can look at Chip Pearson's site for code that essentially
checks the conditional formatting conditions.

http://www.cpearson.com/excel/CFColors.htm

--
Regards,
Tom Ogilvy

raymondsum <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"raymondsum" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>
> 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
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>