Conditional Formatting Changes Cell Color, Then Claims Cell Is Still Uncolored

Occasional Contributor

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


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. 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???

5 Replies
best response confirmed by and_rogynous (Occasional Contributor)


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


Click Format...
Activate the Fill tab.
Set the color to pink.
Click OK, then click OK again.


Repeat these steps, but with the formula


and with aqua as color.


Etc., with the ninth and last rule using


This is amazing!!! Is there a way to prevent this from filling in all the blank cells within the range? Thank you so much!


Select the range again, and make sure that B3 is the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...

Select each of the rules in turn and click Edit Rule...

Change the formulas from for example







You are a life-changer, honestly.
Another follow-up question...does this only work on certain versions of Excel? This is for a shared document at work, and when my coworker opened the file it was all only pink.
Thanks again!


The UNIQUE function is available in Excel in Microsoft 365 and Office 2021 (and in Excel Online), but not in older versions.

To make it work in all versions, use