Conditional formatting with condition

**RAJESH SHAH** · 08-01-2022, 01:19 PM

Hello experts,
I have posted this query in different forums and I am not able to solve it though. I am trying to color the rows where the amounts are repeated more than twice under one ID No. I am not even able to get the first condition of the 2 conditions right.
I tried and entered this formula in the new rule to get the first condition right but it seems I am missing something.
=COUNTIFS(C$2:C$20000,C2,G$2:G$20000,G2,H$2:H$20000,H2,I$2:I$20000,I2)>2
The second condition is that it should accept the amounts where the difference is less than 1/- between the same amounts under one ID No. like in the workbook.

The Link to the same query in the other forum.
https://www.mrexcel.com/board/thread...ition.1212126/

**RAJESH SHAH** · 08-04-2022, 12:39 AM

As I haven't received any reply to my post which may be due to the migrating, FYI, I have shared this query on a different forum.
https://forum.ozgrid.com/forum/index...36#post1256736

**FDibbins** · 08-04-2022, 01:00 AM

I cant see your rule in any cell, can you point me to where you have it?

**RAJESH SHAH** · 08-04-2022, 01:10 AM

I tried the formula in the sheet but it didn't work. I need help with the formula. I have colored the cells manually to show the end expected result.

**FDibbins** · 08-04-2022, 01:17 AM

OK what exactly are you testing for? Your formula seems to include testing in all columns, but what is the basic criteria?

**RAJESH SHAH** · 08-04-2022, 01:21 AM

Under each ID there are rows which contain certain amounts. I am trying to color the rows where under one ID the similar amounts appear more than twice and if the difference between the amount is 1/- ruppee, then too it should accept it as similar amount

**RAJESH SHAH** · 08-04-2022, 01:22 AM

Even a code will help if possible. Please Compare rows C, G, H and I only and color the rows from A: L

**RAJESH SHAH** · 08-06-2022, 08:13 AM

This is to inform you that the issue has not yet been resolved in any of the forums. If it is solved I will update this post without fail with the solution.

**JeteMc** · 08-10-2022, 02:22 PM

For the data shown in the file attached to post #1, use the following formula for the conditional formatting rule:

Formula:

Please Login or Register  to view this content.

Note that the applies to is: =$C$2:$L$23
Let us know if you have any questions.

**RAJESH SHAH** · 08-10-2022, 02:38 PM

JeteMc. That is perfect. Finally, after posting on different forums for the solution for nearly a week you finally solved it man.
Thank you so much.

**JeteMc** · 08-10-2022, 05:43 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

**RAJESH SHAH** · 08-22-2022, 09:45 AM

Hello experts
This formula was shared by JeteMc and it is working perfect. But to correct one issue, I need your help once again to edit the formula in such a way that when I apply conditional formatting it should color the rows which appear 3, 5 or 7 times only under one GSTIN. If the amounts under one GSTIN number appears 2,4,6, or 8 times I don't want to color it.
The formula used earlier in the new rule was
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))>2

**RAJESH SHAH** · 08-23-2022, 05:50 AM

JetMc. Can you please help me to change the count and edit the code if possible.? like if
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3 & 5 & 7 & 9
I am not sure how to place the condition here.

**JeteMc** · 08-23-2022, 09:26 AM

Taking in mind that your needs may change again I suggest:
1. Make a table listing the number of repetitions to be highlighted
2. Use the following as a conditional formatting rule:

Formula:

Please Login or Register  to view this content.

Note that if you add to or delete from the table the formula will automatically adjust.
Let us know if you have any questions.

**RAJESH SHAH** · 08-23-2022, 09:41 AM

JeteMc. Is it possible to write a formula without the help column Q. The sheet I am trying to color the rows yellow is created after the code runs. So, I have to convert the formula into a code and then add that in the already existing code of the workbook. So, when the final code is played, it will create the sheet with the data and also color the cells Yellow.

**RAJESH SHAH** · 08-23-2022, 09:44 AM

If I have to apply multiple times CF for the same range then too it will be helpful.
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=5
and so on.

**RAJESH SHAH** · 08-23-2022, 09:47 AM

The name of the sheet in the original workbook is Combined Data and this is the code of the formula you shared before, which will help me to color the rows yellow which appear more than twice., sort the data and get me the yellow rows in the top.

HTML Code:

**JeteMc** · 08-23-2022, 10:48 AM

Your idea to apply CF multiple times to the same range (post #16) seems reasonable, however I don't know enough about VBA to say how to translate the formulas into code.

**RAJESH SHAH** · 08-23-2022, 10:53 AM

You can just share the perfect formula to color 3 rows, 5 rows. etc., The code is not a problem. I can get the formula converted into code.
Getting the right formula is the tough part.

**RAJESH SHAH** · 08-23-2022, 10:57 AM

=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3
I tried by changing <2 to =3 it is working.
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=5
This is working too.
Is it possible to replace =3 with odd numbers in a formula? Obviously except 1.

**RAJESH SHAH** · 08-23-2022, 01:03 PM

JeteMc. Hope you are still online.
I tried to apply the formula like this
=SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3&5&7
The formula is accepted without error but it is not coloring the cells yellow. Can you correct the end portion of the formula Please?

**JeteMc** · 08-23-2022, 01:14 PM

Try:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**RAJESH SHAH** · 08-23-2022, 01:18 PM

Eureka. Got it right. Thank you very much JeteMc.

**JeteMc** · 08-23-2022, 02:43 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

**RAJESH SHAH** · 08-23-2022, 02:45 PM

You too have a wonderful day.

Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Re: Conditional formatting with condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Condition Formatting Help

[SOLVED] I need 4 condition for condition formatting

Bookmarks

Bookmarks

Posting Permissions