Forum Discussion
Solved
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 * ...
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
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
=MOD(COUNTA(UNIQUE($B$3:$B3))-1,9)=0
to
=AND(B3<>"",MOD(COUNTA(UNIQUE($B$3:$B3))-1,9)=0)
- 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
=AND($B3<>"",MOD(SUM(1/COUNTIF($B$3:$B3,$B$3:$B3))-1,9)=0)
=AND($B3<>"",MOD(SUM(1/COUNTIF($B$3:$B3,$B$3:$B3))-1,9)=1)
etc.
