Unique ID's with a few restrictions

[Xiq]

Dear Macro Knowledge Persons and others!

I’m looking for a macro solution for creating unique ID numbers with a few restrictions.

First of all, please see attached file for a sample-data with an example of my expected result after the use of the macro.

Here are the restrictions/functions I would like:

• Range: either a single sell, selected (possible filtered) range or a complete column.
• Only enter an ID value if the “Type” is equal to “Not Sunny” (column A)
• Only enter an ID value if the cell is empty
• Only enter an ID value if the selected cell/range/column is equal to the “ID”-column (column B)
• The new ID value should be unique in the entire column
• Format the new ID values with a red text color.

Many thanks in advance!

--Xiq

 

[Abdul Matheen]

Hi,

I have couple of questions before i give you the solution.

1) The ID is any 4 digit number or need to calculate the number?
2) Could you rephrase the 4th point in your bullet point?



Regards
Abdul Matheen

 

[Xiq]

Hi Abdul Matheen,

1. Neither. At the moment I already have unique numbers in the range of 1 to 11543, so the next unique number could be in this case somewhere between 11544 to infinity (though infinity is a bit unpractical). My guess would be to first check the max value in column A and add 1 for each cell that meets the criteria.
2. Basically I don't want to happen anything if the selection is not positioned in column B only. For exampe a selection of B2:C100 should do nothing at all.

Hope this makes things a bit clear

 

[Abdul Matheen]

Hi,

I have created the macro check the attached workbook.

Let me know if you have any more questions.

Regards
Abdul Matheen

 

[Xiq]

@Abdul Matheen

The functionality seems to work very nicely! But it does not restrict itself within a selection or whenever the selection is out of column B.