Conditional formatting issue

[snewsome]

Hey everyone, I'm having an issue with conditional formatting. I am using this formula, and it works great, only problem is blank cells are formatted as well. Any help would be appreciated...I'm basically having duplicates highlighted based off of another worksheet.

=SUMPRODUCT(--(G1=Sheet2!$U$1:$U$41))

 

[iggydarsa]

would this help

=AND(G1<>"", SUMPRODUCT(--(G1=Sheet2!$U$1:$U$41)))

 

[snewsome]

would this help

=AND(G1<>"", SUMPRODUCT(--(G1=Sheet2!$U$1:$U$41)))

I was just about to respond to my first post. I added another CF rule to format cells that contain blank value, which I think has worked. Thanks so much!!

 

[snewsome]

would this help

=AND(G1<>"", SUMPRODUCT(--(G1=Sheet2!$U$1:$U$41)))

For some reason the "applied to" field when I use the CF formula updates to exclude cells after a certain point. If I apply the CF to say cell G3:G1500..and I start entering at cell 200, when I check the applied field, it updates to G3:G199, then if I enter in G200 through G210, the applied field will adjust to G3:G199,G211:G1500 which excluded returning a CF to the 10 cells I just entered?? Haven't seen this before, can anyone help how to lock the CF cells?

 

[iggydarsa]

It happens when you copy/paste, when you paste it takes the formatting of the original cell and overwrites CF. Are you copy/pasting or manually entering?

 

[snewsome]

It happens when you copy/paste, when you paste it takes the formatting of the original cell and overwrites CF. Are you copy/pasting or manually entering?

Correct. I'm copy/pasting..

 

[iggydarsa]

So thats the problem - maybe when you paste do a "Special Paste": paste values only