I'm using a small VBA function to allow conditional formatting based on cell color.
The VBA I'm using for this is:
Function IdentifyColor(CellToTest As Range)
'Returns R + (256 * G) + (65536 * B)
'IdentifyColor = 255 for red, 65280 for green, etc.
IdentifyColor = CellToTest.Interior.Color
End Function
Within a table, each order (order number is the first column) will have multiple rows. This means there will be some number of rows (usually between 1 and 10) with the same order number, and then moving on to the next order number the same thing will occur (rows might begin with 1, 1, 1, 2, 2, 2, 2, 2, 2, 4, 7, 15, 15, 18, etc). The order numbers are not necessarily sequential, but they will never repeat. I want the row colors to alternate between 9 colors (I have both HEX codes and Excel color codes) based on order number (order 1 will be pink, order 2 will be aqua, order 4 will be orange, etc). But, I want a specific color order to remain, no matter how the table is sorted. So if I sort it by customer name, which will not be the same as by order number, I still want the first order to be pink, the second order to be aqua, etc...this will prevent 2 orders of the same color appearing next to each other when the table is sorted a certain way.
The top left cell of the table header row is B2, and the table spans from column B to column N).
Excel color 16777215 is the default white, and 13547241 is the pink I want to start with.
So far, I have 2 conditional formatting rules:
Formula: =IdentifyColor(B2)=16777215 // Format: [cell shading becomes pink] // Applies to: =$B$3:$N$3
and
Formula: =AND(IdentifyColor(B3)=13547241,B3=B4) // Format: [cell shading becomes pink] // Applies to: =$B$4:$N$4
The first rule seems to work fine; the entire row is shaded pink (because the header row is default white). The second one does not, though, and I think I know why: when I type =IdentifyColor(B3) into an empty cell, it returns 16777215, even though the cell is technically 13547241 because of the previous rule.
So...how do I get Excel to recognize the color of a cell from conditional formatting, so I can then use that property in other conditional formatting rules???
