How to get a border to separate 'ID'?

Hi Friends,

In the attached workbook the macro is generating the serial numbers but the serial numbers are repeating for same ‘ID’ which is in column ‘B’. If the ‘ID’ in column ‘B’ is same then I want a serial number for only first ‘ID’ and the rest should be blank. I also want a thick horizontal border to separate ‘ID’. The require output is shown on the ‘output’ sheet.

Any help will be highly appreciated.

Thanking you in anticipation.

Non VBA solution:

for the first question

a2= =if(countif($B$2:B2,$B2)=1,MAX($A$1:$A1)+1,"") and drag down


for the second question I have no answer

Hi oeldere,

Perfect! Thank you.

For the second border problem assistance requested.

Hi - if you put the following in your Sheet1 object that should do it.

Please Login or Register  to view this content.

Hope that helps.

Hi MatrixMan,

Thank you. No, it's not as per output shown on 'output' sheet.

Doesn't matter. I used conditional formatting to get border. However, I didn't get a thick border there. Is it possible to change the particular horizontal border thick by code?

Thank you and have a nice time.

As far as I'm aware, you can't do thick borders in conditional formatting. The output from the code is the same as the output sheet except for the blank rows at the end, which I thought were a mistake. I'm sure you can modify it to add them in if you like.

Yes, I agree with you. So I asked you that can we change the particular horizontal border to thick border by a small code because there is already a lot of codes.

Have a nice time.

This will search column A of your sheet with conditional formatting and put a thick top border above an cell with conditional formatting.

Please Login or Register  to view this content.

Thank you. I'm getting compile error 'invalid use of me key word on the following line

Please Login or Register  to view this content.

Thanking you,

That's because (I suspect) you've put the code into a standard module rather than the sheet object of the worksheet you're formatting. If you must have it in a module then you need to replace "me" (meaning "this sheet") with the explicit reference to the sheet.

So - define a worksheet that you want to write to:

Please Login or Register  to view this content.

And then do a replace within this procedure only (CTRL+H) swapping "me" for wksTarget.

Hi MatrixMan,

I didn't understand the instruction. I don't have any problem to put the code in sheet or standard module. Will you please attach a workbook with amended code for my better understanding and avoid any mistake from me please?

Sorry for trouble.

Thanking you.

If you send me the file with the code you're running I'll fix it for you.

Hi Matricman,

Thank you for your kind concern about my problem.

Hi Jindon,
Thank you. It's working fine. Sorry to say that I have inserted 3 rows above it for company title and other information which I forgot to mention and show in the attached workbook in my previous post. Please will you do the amendment in the code to meet these new modification? Now instead of row 2 the first number starts from row 5 as shown in the attached workbook. I have attached the workbook for your perusal to amend the code.

Sorry for trouble.

Thank you to both of you and have a nice time.

Hi Jindon,

Perfect! Thank you.