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)

 

[6StringJazzer]

Please show the conditional formatting rule you are using. I am thinking the most straightforward thing is to include a test for "" in the rule. For example, if your rule is:

=$A1="Absent"

then make it

=AND($A1="Absent",X1 <>"")

But it's hard to say without more information about what you are doing.

 

[rkrt1]

Please show the conditional formatting rule you are using. I am thinking the most straightforward thing is to include a test for "" in the rule. For example, if your rule is:

=$A1="Absent"

then make it

=AND($A1="Absent",X1 <>"")

But it's hard to say without more information about what you are doing.

I am using this method

 

[Peter_SSs]

I am using this method

Don't use that method so remove that rule.
Select X2:X(as far as your results might ever go or further) and then use New Rue -> Use a formula to determine which cells to format and use this formula changing the X$100 to match the last cell in your selection

=AND(COUNTIF(X$2:X$100,X2)>1,X2<>"")

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditional Formatting
and https://www.excelforum.com/excel-general/1386315-conditional-formatting.html
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rkrt1]

Don't use that method so remove that rule.
Select X2:X(as far as your results might ever go or further) and then use New Rue -> Use a formula to determine which cells to format and use this formula changing the X$100 to match the last cell in your selection

=AND(COUNTIF(X$2:X$100,X2)>1,X2<>"")

Thanks @Peter_SSs . It worked. Amazing.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.