Conditional Formatting with a Dynamic Range

We have a list of units in Column A that are going to be renamed to whatever is listed in the adjacent cell of column B.

We can rename our units in bulk using a separate utility, but need to make sure that when we do so, no two units ever have duplicate values.

The units to be renamed come in to us in random order so the first thing we did was sort by what the new unit numbers will be (column B).

I would like to set up conditional formatting so that we know where to stop as we bulk rename our units.

For example, we can bulk rename units listed in rows 2 through 11 without a problem because it doesn't matter what order our renaming utility does it in. However, if we included row 12 in the mix, then there's a chance the utility would rename Unit D to H (Row 12) before the Unit H (from row 5) had been changed to W and we'd have two H's.

So, I'm trying to create conditional formatting that will show stopping points for each bulk rename that we need to do. In the example I'm providing, it would highlight Row 12 to indicate a stopping point. After this is where it gets tricky. It then needs to start looking at a new (dynamic?) range based off of where we had last identified a stopping point.

In other words, there's no reason for Row 13 to be highlighted anymore as we will assume that cells from rows 2-11 have all been renamed and the G (from row 7) will now be a T. Instead, the next row to be highlighted should be Row 16 where B can not be renamed to D until the D from row 12 has been renamed to H.

Continuing this pattern we shouldn't see another highlight/stopping point until Row 31, and then again on row 35, and 37.

To get what you see in the image, this is what I had used in the Formatting Rule:
=IF(COUNTIF($A$1:A1,B1),"TRUE", "FALSE")
Applied to: =$B:$BExcel.PNG

Thanks in advance for any tips and/or suggestions.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.