Conditional formatting of visible cells Excel 2010

as a way with user difined function

Please Login or Register  to view this content.

Tim Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

cmikec, welcome to the forum.

An alternative, if this is only going to be done occasionally and/or you don't want to use VBA, is as follows:

First, add 'Select visible cells' to your Quick Access Toolbar (QAT):

• Click the down arrow at the end of the QAT and select 'More Commands'
• Under 'Choose Commands from' select either 'All Commands' or 'Commands not in the ribbon'
• Scroll down to find 'Select Visible Cells'
• Click 'Add' then 'OK'

Now, clear the 'duplicates' conditional formatting (CF) from your column.
Select your column as normal, then click the 'Select Visible Cells' button.
Now do your CF to highlight duplicates again. This time it'll only be applied to the visible cells.

I hope that's useful.

Another approach would be the little used and mostly forgotten old Excel4 Get.Cell 'macro/function'.

One of the parameters that the Get.Cell can return is the row height, something which appears to have been overlooked with the CELL() function which will return a cell width but not the row height. If we know that the row height is zero then that allows us to use work out the duplicates you want to highlight.

Create a name called say CountVisible and define it as

Formula:

Please Login or Register  to view this content.

Now in a helper column alongside your data enter the formula =CountVisible and copy it down.

The attached example consists of column A which allows you to pick a subset of data to demonstrate the process, column B which contains the values you want to check and the helper column C

Then create a conditional format in B2 and apply it to the whole range

Formula:

Please Login or Register  to view this content.