Convert formula into code

[RAJESH1960]

Hello experts,
This is JohnnyL's code and I need some help to correct some issues and also add one line of code in the end to update the application.
I have tried to address the issue as simple as possible in the conditions sheet.
The formula to color the cells in the combined data sheet is set for 218 sheets in this file. The range of rows will vary each time from 100 to 20,000 rows in different scenarios. So, instead of writing the code for a fixed range, if the code can count the number of rows in the Combined data sheet it will be really cool.
The formula is for ____Under one Gstin number, in the combined data sheet color rows which have the same amounts repeated more than 2 times this formula color the cells (Color Yellow) from A: N if applied in CF. I have colored manually to show the result.

=SUMPRODUCT(($C$2:$C$218=$C2)*($G$2:$G$218>$G2-1)*($G$2:$G$218<$G2+1)*($H$2:$H$218>$H2-1)*($H$2:$H$218<$H2+1)*($I$2:$I$218>$I2-1)*($I$2:$I$218<$I2+1))>2

Match Portal with 2B.xlsm

 

[RAJESH1960]

Ok. I will try that.

 

[RAJESH1960]

It worked. First I ran the convert 2B portal then your new module. Both worked perfectly.

 

[RAJESH1960]

f condition to the code, if found then color the rows and sort by yellow color else do nothing, just sort the data.

I don't see the if condition in the code. I hope it works if when there are rows not more than 2 rows also, amounts in one GSTIN number appearing just twice.

 

[johnnyL]

In the VBA project I have inserted your module and saved it. In the Match portal where do I insert
call Add_CF_FormulaThenSort..?

I guess an answer to that could be at the end of 'Sub Match_Portal_Tally()'

Make it look like:

'
    Call CheckSubTotal
'
    Call Add_CF_FormulaThenSort
'
    Application.ScreenUpdating = True                                                               ' Turn ScreenUpdating back on
'
    If Sheets("Mismatches").Range("B2") = "" Then MsgBox "No MisMatches Found"                      ' If No MisMatches Found, tell the user
End Sub

 

[johnnyL]

I don't see the if condition in the code. I hope it works if when there are rows not more than 2 rows also, amounts in one GSTIN number appearing just twice.

Test it and let us know.

 

[RAJESH1960]

I am getting a compile error after adding the line call Call Add_CF_FormulaThenSort
Expected variable or procedure. Not module.

 

[RAJESH1960]

    Call CheckSubTotal
'
    Call Add_CF_FormulaThenSort
    Application.ScreenUpdating = True                                                               ' Turn ScreenUpdating back on
'
    If Sheets("Mismatches").Range("B2") = "" Then MsgBox "No MisMatches Found"                      ' If No MisMatches Found, tell the user
End Sub

 

[RAJESH1960]

Before the error, Portal sheet is created though..

 

[johnnyL]

Don't know what to suggest, it is working fine on this end.

 

[RAJESH1960]

Ok. I will close and re open the application and keep trying. Please share your workbook.