microsoft excel - Format row that is different than the preceding VISIBLE row

I have an Excel sheet as a database, where each row has a category, and are always sorted by category.

      Category     Item
1     AAA          jjj
2     AAA          kkk
3     AAA          lll
4     BBB          mmm
5     BBB          nnn
6     CCC          ooo
7     CCC          ppp
8     CCC          qqq
9     DDD          rrr

I use conditional formatting to visually group the categories: e.g. the first row for a new category should have a border above it, and the category value should be bold. I do this via a formula applied to the row like = $A2<>$A1 and it works perfectly...until I use filtering to hide rows.

For example, if filtering hides row 6/CCC/ooo then rows 7 and 8 appear to be part of the BBB category.

Is there any way to create a conditional formatting formula that says "if the value in this cell is different from the value in the first VISIBLE row above me"?

Answer

Edit: This is not an ideal answer. It requires that the rows are sorted by category; if they are not, it will fail. It also requires adding an extra column (that you may hide) used to determine if the row is hidden or not.

I will prefer and accept any answer that matches the title of the question, i.e. based on the value of the preceding visible row, not the first occurrence of the value.

---

Format First Occurrence of a Value

1. Add a 'visible' column that calculates if your row is visible, using the formula:

   
   

   SUBTOTAL(103,B2)
   

   
   

   ...where 103 is a "magic number" (it indicates the COUNTA function, ignoring visible cells), and B2 is the column in the same row with your category. You may then hide this column.




2. Use a conditional formula like this one:

   
   

   =COUNTIFS($B$1:$B1,$B2,$D$1:$D1,1)=0
   

   
   

   ...where B is the column with your category, and D is the column with your 'visible' formula in it.

How it Works

It counts how many times the current category value ($B2) has been seen (visible) previously in the column, from the first row up until the row preceding this one. If that value is 0, then you can apply custom formatting.

In Action

Here I've applied one conditional formatting across the entire row to add the border above, and another just to the category column to bold and darken the first category name. You can 'hide' repeated category values by setting them to white; I've included them here just to clarify what's going on.