Conditional formatting

[bojan0810]

I am trying to highlight duplicates in 2 columns...

To be specific, I need to look duplicates from first column in second column.

So if for example, if cell in column B appears in column D it will highlight it.

Highlight duplicates doesnt work this time for me (I mean it works but I dont need like that), because on first column I have duplicates and I dont want to highlight that duplicates, I just want to highlight duplicates that appears in other column.

I am trying to use formula. I did few of them and every time it highlights cell above the correct answer. I am not sure why is that doing.

For example, first column first cell(under the heading) is duplicate and it highlight heading not that cell. And same is for other cells.

I hope someone will know the answer (Claus lol)

Thanks

 

[Claus Busch]

Hi,

Am Wed, 11 Jun 2014 22:49:20 -0700 (PDT) schrieb (e-mail address removed):

I am trying to highlight duplicates in 2 columns...

To be specific, I need to look duplicates from first column in second column.

So if for example, if cell in column B appears in column D it will highlight it.

select your data in column D and use
=COUNTIF(B:B,D1)>0
and choose your format


Regards
Claus B.

 

[bojan0810]

Hi Claus. Thanks

I tried with that formula, and few others and it always highlights cell above the right one.

I have list of website in column B and another list of websites in columd D, I need to compare both of them and highlight all websites in column B that appears in column D.

Every formula I tried it highlights cell above the right one.

 

[Claus Busch]

Hi,

Am Wed, 11 Jun 2014 23:03:10 -0700 (PDT) schrieb (e-mail address removed):

I tried with that formula, and few others and it always highlights cell above the right one.

I have list of website in column B and another list of websites in columd D, I need to compare both of them and highlight all websites in column B that appears in column D.

Every formula I tried it highlights cell above the right one.

select the cells you want ot highlight. The formula must refer to the
active cell ( the lighter cell) of the selection.
Please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "ConditionalFormatting"


Regards
Claus B.

 

[bojan0810]

Dana Äetvrtak, 12. lipnja 2014. 08:05:48 UTC+2, korisnik Claus Busch napisao je:

Hi,



Am Wed, 11 Jun 2014 23:03:10 -0700 (PDT) schrieb (e-mail address removed):






select the cells you want ot highlight. The formula must refer to the

active cell ( the lighter cell) of the selection.

Please have a look:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "ConditionalFormatting"





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks for helping me.

I saw now what I was doing wrong. Even I dont know why excel is doing that....

I added conditional formatting to whole column (B:B) and it highlights cellabove like that. I changed it to (B2:B-max) and it works now. Weird...


Anyway thanks. Also, is there any way to "lock" or protect or whatever, conditional formating so when somone copy paste into it, that it works again. I know that I can paste it as values and conditional formatting will stay, but if you paste normal, conditional will not be there.

 

[Claus Busch]

Hi,

Am Wed, 11 Jun 2014 23:16:19 -0700 (PDT) schrieb (e-mail address removed):

Anyway thanks. Also, is there any way to "lock" or protect or whatever, conditional formating so when somone copy paste into it, that it works again. I know that I can paste it as values and conditional formatting will stay, but if you paste normal, conditional will not be there.

you can lock cells but not CF. Run the macro recorder while creating the
conditional formatting. If someone paste values to this column run the
macro.
Or highlight the cells with Worksheet_Change-Event instead of CF.


Regards
Claus B.

 

[bojan0810]

Dana Äetvrtak, 12. lipnja 2014. 08:25:48 UTC+2, korisnik Claus Busch napisao je:

Hi,



Am Wed, 11 Jun 2014 23:16:19 -0700 (PDT) schrieb (e-mail address removed):






you can lock cells but not CF. Run the macro recorder while creating the

conditional formatting. If someone paste values to this column run the

macro.

Or highlight the cells with Worksheet_Change-Event instead of CF.





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

I added formula to vba and used like you said worksheet change and it worksgreat.

Thank you again for helping me

 

[Claus Busch]

Hi,

Am Wed, 11 Jun 2014 23:52:46 -0700 (PDT) schrieb (e-mail address removed):

I added formula to vba and used like you said worksheet change and it works great.

you can also have another look in OneDrive. I made highlighting via VBA.
You have to rightclick and download the file because in OneDrive macros
are disabled.


Regards
Claus B.

 

[bojan0810]

Thanks for that.

I made code like this one.

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("b:b")

.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTIF($D:$D,B1)>0"
.FormatConditions(1).Interior.ColorIndex = 7
End With
End Sub

 

[kumarapush]

What is the excel version that you are using? If it is a version 2003 or higher, it has built in option in Conditional Formatting to highlight the duplicates.