Conditional Formatting changes when I sort the data.

[excelnow]

Hi,

I have a column of data and I have a conditional formatting rule for highighting duplicate values. So, at first I apply the rule to the whole column ($C:$C), but then when I sort the column etc., the rule changes to something like this:

=$C$1:$C$2173;$C$2175:$C$2183;$C$2185:$C$1048576

It seems, it skips one or two cells when I sort the column. Is there any way to prevent this?

 

[Gerald Higgins]

I tried replicating this, but couldn't.

Can you post the full version of the original rule, and the full version of what it becomes ?

 

[jasmith4]

Conditional formatting is tricky, because you have to pay even more attention than normaly to relative and absolute addressing -- those pesky dollar-signs! But here's what worked for me:

First, select only C2 (I'm assuming C1 is a header) and pull down Format / Conditional Formatting. The dupe-coloring condition should be Formula Is and (careful!) =COUNTIF(C$2:C2,C2)>1. Next, with only C2 still selected, hit Ctrl-C to copy and Ctrl-space to select the whole C column. Now pull down Edit / Paste Special and select only Formats. Your C column will then behave as you want, even as you sort it both ways, insert or delete cells or rows, whatever.

To see why, click any cell below C2 -- let's say C10 -- and pull down Format / Conditional Formatting. Its condition has become =COUNTIF(C$2:C10,C10)>1, because the 2 after the only dollar-sign didn't change. That is, each cell in column C looks for dupes from the second row ($2) to the current row (10 in C10).

 

[excelnow]

Conditional formatting is tricky, because you have to pay even more attention than normaly to relative and absolute addressing -- those pesky dollar-signs! But here's what worked for me:

First, select only C2 (I'm assuming C1 is a header) and pull down Format / Conditional Formatting. The dupe-coloring condition should be Formula Is and (careful!) =COUNTIF(C$2:C2,C2)>1. Next, with only C2 still selected, hit Ctrl-C to copy and Ctrl-space to select the whole C column. Now pull down Edit / Paste Special and select only Formats. Your C column will then behave as you want, even as you sort it both ways, insert or delete cells or rows, whatever.

To see why, click any cell below C2 -- let's say C10 -- and pull down Format / Conditional Formatting. Its condition has become =COUNTIF(C$2:C10,C10)>1, because the 2 after the only dollar-sign didn't change. That is, each cell in column C looks for dupes from the second row ($2) to the current row (10 in C10).

Thanks for the reply but I use Office 2007 and I couldn't follow your directions.

 

[jasmith4]

OK, here's a rewrite -- this is Excel 2010:

First, select only C2 (I'm assuming C1 is a header) and in the Home ribbon, drop down Conditional Formatting, choose New Rule, choose Use a formula and enter the formula =COUNTIF(C$2:C2,C2)>1. Next, with only C2 still selected, hit Ctrl-C to copy and Ctrl-space to select the whole C column. Now drop down Paste on the Home ribbon and choose the first of the Other Paste Options, whose tooltip is Formatting(R). Your C column will then behave as you want, even as you sort it both ways, insert or delete cells or rows, whatever.

 

[excelnow]

OK, here's a rewrite -- this is Excel 2010:

First, select only C2 (I'm assuming C1 is a header) and in the Home ribbon, drop down Conditional Formatting, choose New Rule, choose Use a formula and enter the formula =COUNTIF(C$2:C2,C2)>1. Next, with only C2 still selected, hit Ctrl-C to copy and Ctrl-space to select the whole C column. Now drop down Paste on the Home ribbon and choose the first of the Other Paste Options, whose tooltip is Formatting(R). Your C column will then behave as you want, even as you sort it both ways, insert or delete cells or rows, whatever.

I did up to entering the formula but then lost the track again. In 2007 there is no Other Paste Options in Paste drop down menu.

By the way, I guess I forgot to mention that I have a piece of code that I am using for that sheet. And I suspect it might be the cause of the problem as well.

Private Sub Worksheet_Change(ByVal Target As Range)

        ActiveCell.Replace What:="www.", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Selection.Hyperlinks.Delete

End Sub

This code removes "www." from the text I paste in a cell and removes the hyperlink.

 

[jasmith4]

I think you can pull down Conditional Formatting and Manage Rules to change the range to which the rule applies: change it to the entire C column.

 

[excelnow]

I think you can pull down Conditional Formatting and Manage Rules to change the range to which the rule applies: change it to the entire C column.

I am already doing this. But when I paste a couple of entries, it gets broken again and gets a value like =$C$1:$C$2173;$C$2185:$C$1048576.

 

[jasmith4]

You mean the range to which the rule applies gets that value? Then I guess you have to change it back to "=$C:$C" every time. Maybe there's something about how you paste: you're really inserting what you copied, right? Maybe you can insert blank cells first, whereupon the rule's range wouldn't change -- then when you're copying and pasting, don't use the normal paste: paste only values or formulas instead.

 

[excelnow]

You mean the range to which the rule applies gets that value? Then I guess you have to change it back to "=$C:$C" every time. Maybe there's something about how you paste: you're really inserting what you copied, right? Maybe you can insert blank cells first, whereupon the rule's range wouldn't change -- then when you're copying and pasting, don't use the normal paste: paste only values or formulas instead.

Changing it every time or the other way will take a lot of time, that's why I am using a little code and formatting. I attached a sample excel file here:

http://www.nailyener.com/wp-content/uploads/2011/06/Book.xlsm

I don't expect this from you but if you could have a look, maybe you could notice something that I can't see.

As you see in the example file, there are a few entries in column A. Please view the code of that sheet and check the formatting rules first. Then copy a web address from your browser into a cell of column A. You will see that formatting rule will change.

Thanks.