Conditional Formatting

[rkrt1]

1) I am using drop down menu which shows huge list of information spread over 20 columns and shows results maximum till the 50th row
2) The resulted list is variable. Let's say If i choose "AAA" from drop-down, it shows 10 rows, If I choose "BBB" it shows 5 rows, "CCC" it shows 50 rows, etc.
3) I have applied conditional formatting for duplication on all columns till the 50th row
4) Since the resulted rows' count is variable (point 2), the conditional formatting highlighted color is spread over the whole range at maximum i.e 50th row

What I Need:
I want conditional formatting's highlighted color to be shown only till the row till which that selected option (point 2) is displaying the result. For example: In the attached image, I want conditional formatting color to be shown till row 15 (Pankaj) and not exceeding row 15. Similarly, when I choose another option (point 2) and if it displays the result only till row 3, conditional formatting should be applied only till row 3 and not exceed (like in the image it's exceeding)

C

 

[p45cal]

What is the conditional formatting you have now? A formula? Let's have it.
What range is it applied to?

 

[alansidman]

crossposted: https://www.excelforum.com/excel-general/1386315-conditional-formatting.html

 

[rkrt1]

Thanks Alan for the headsup.

Will be careful going forward.

 

[rkrt1]

What is the conditional formatting you have now? A formula? Let's have it.
What range is it applied to?

I have checking duplicates conditional formatting.

It's applied on 20 columns till the 50th row. as the maximum data that can be retrieved when selecting an option is till 50th row.

 

[p45cal]

1. Conditional formatting for duplicates doesn't highlight duplicate blank cells so there must be something in those apparently blank cells. Can you tell us what? It could be a case of making sure blank cells really are blank. Maybe changing how things are filtered. Perhaps best attach (a mock up of) your workbook where this is happening.

2. Confirm that here and at ExcelForum are the only places you have posted this topic (or very similar) to.

 

[Pecoflyer]

Also Xposted at https://www.mrexcel.com/board/threads/conditional-formatting.1213807/

 

[Roger Haney]

Until you post your formula itself and the ranges to which it is applied as requested, it may be a while until you get an accurate answer. But I will make a preliminary suggestion based on the information from you so far. Highlight the entire range of your data which I will arbitrarily assume to be from $A$1:$CZ$50. Then use this formula =(ISTEXT(A1))*(COUNTIF($A$1:$CZ$50,A1)>1).