Conditional Formatting Changes Cell Color, Then Claims Cell Is Still Uncolored
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???
CellToTest.Interior.Color ignores conditional formatting, it returns the color as if no conditional formatting rules have been applied. You can use CellToTest.DisplayFormat.Interior.Color to get the color as actually displayed taking conditional formatting into account.
But I'd do it differently, without any VBA.
Let's say your data are in B3:N1000.
Select this range. The active cell in the selection should be in row 3.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=MOD(COUNTA(UNIQUE($B$3:$B3))-1,9)=0
Click Format...
Activate the Fill tab.
Set the color to pink.
Click OK, then click OK again.Repeat these steps, but with the formula
=MOD(COUNTA(UNIQUE($B$3:$B3))-1,9)=1
and with aqua as color.
Etc., with the ninth and last rule using
=MOD(COUNTA(UNIQUE($B$3:$B3))-1,9)=8