Conditional Formatting

[msche09]

Hello,

I have been searching high and low for the answer and I don't seem to see anything. Perhaps I missed it.

I am trying to conditional format the second value only in my worksheet. I would like excel to locate any duplicate value from many different cell ranges within my worksheet and if there is any duplicate value to highlight the second value only.

If I was to delete one of the values the conditional formatting would return to normal.

Thanks in advance.

 

[Joe4]

Are we talking about a specific value, or any number of values?

Are we looking in just one column, or multiple columns and multiple rows?

If we are talking about multiple columns AND multiple rows, then the order really matters in determining which is first and which is second.
Are we to look from left-to-right (down column A, before looking in column B, then to column C, etc), OR are we to look from top-to-bottom (across row 1 before looking to row 2, row 3, etc)?

It might be helpful to see a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[msche09]

Are we talking about a specific value, or any number of values?

Are we looking in just one column, or multiple columns and multiple rows?

If we are talking about multiple columns AND multiple rows, then the order really matters in determining which is first and which is second.
Are we to look from left-to-right (down column A, before looking in column B, then to column C, etc), OR are we to look from top-to-bottom (across row 1 before looking to row 2, row 3, etc)?

It might be helpful to see a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello,

The second value would be determined based on the second entry into the worksheet. If I input a value in a cell and there is no other value of the same it wouldn't change. If the value is equal to another value in the worksheet it would change to another formatting (red background for example). Would this be possible?

The values are employee names.

 

[Joe4]

Please answer my other two questions:

Are we looking in just one column, or multiple columns and multiple rows?

If we are talking about multiple columns AND multiple rows, then the order really matters in determining which is first and which is second.
Are we to look from left-to-right (down column A, before looking in column B, then to column C, etc), OR are we to look from top-to-bottom (across row 1 before looking to row 2, row 3, etc)?

 

[msche09]

Hello,

I am fairly new to this. I hope I can answer your questions.

I am conditional formatting the following ranges: =$C$8:$C$45,$E$8:$E$45,$G$8:$G$45

I then click on conditional formatting and locate "duplicate values". This will highlight both values with the format. I am looking to only highlight the second value. The second value is determined by the second entry into the worksheet. It is not determined by the first instance in row or column or anything else.

Thanks.

 

[Joe4]

OK, you are clearly not understanding my question. Let's see if I can explain it with an example.

Let's say that the value of "John Doe" is found in BOTH cells C45 and G8.
Which one is considered the "original" and which one is considered the "duplicate"?

(Note that "first" is a subjective description. It depends on whether you working down columns or across rows!)

 

[msche09]

OK, you are clearly not understanding my question. Let's see if I can explain it with an example.

Let's say that the value of "John Doe" is found in BOTH cells C45 and G8.
Which one is considered the "original" and which one is considered the "duplicate"?

(Note that "first" is a subjective description. It depends on whether you working down columns or across rows!)

Hello,

As I mentioned, If I type "John Doe" into G8 first there would be no formatting. If I type "John Doe" into C45 2nd it would format. If I delete "John Doe" from G8, the formatting in C45 would return to default. If I type "John Doe" into E10 (and still have the text in C45) the E10 cell would format. As mentioned, I am not working formatting based on first and second instance based on anything else like columns or rows.

It sounds like the options I am trying to get are not doable with excel.

Thanks.

 

[Joe4]

I thought maybe you were running this against existing data, but it sounds like maybe that you want this to initiate as data is being entered (that might explain why we were not understanding each other). Is that correct?

If so, I don't think you can use Conditional Formatting, as conditional formatting can not identify when the data was actually entered into each cell, it can just rank them based on position on the worksheet (which is the first one found when searching in a particular direction).

You *MIGHT* be able to do something with VBA, have code that automatically run as data is being entered.
We could easily check to see if the data just manually entered in is already found somewhere else on the sheet, and highlight the entry.
However, the manual deletion of a value, and have that unhighlight other already highlighted values, that could prove to be a bit challenging, at least with not having to check every single cell in your desired range every time.

 

[msche09]

I thought maybe you were running this against existing data, but it sounds like maybe that you want this to initiate as data is being entered (that might explain why we were not understanding each other). Is that correct?

If so, I don't think you can use Conditional Formatting, as conditional formatting can not identify when the data was actually entered into each cell, it can just rank them based on position on the worksheet (which is the first one found when searching in a particular direction).

You *MIGHT* be able to do something with VBA, have code that automatically run as data is being entered.
We could easily check to see if the data just manually entered in is already found somewhere else on the sheet, and highlight the entry.
However, the manual deletion of a value, and have that unhighlight other already highlighted values, that could prove to be a bit challenging, at least with not having to check every single cell in your desired range every time.

Hello,

Yes, the data would not be pre-existing. I appreciate the support and apologize for the confusion!

 

[Joe4]

No worries!

Would you like to pursue a VBA solution?