Conditional Formatting the results of a Filter formula

Copper Contributor

I had this functioning, but it got deleted and for the life of me I cannot figure out how to replicate it!

I have an Excel Worksheet CURRENT PROJECTS. In it I have two tabs "data" and "results". 

On the "data" tab, there are several columns including Column A (where the entry options are only "ACTIVE" or BLANK), Column C (a client name in text), and Column G (a text field with the choice of only EXISTING  or NEW).

I have successfully created a FILTER which results in the creation of an array on the "results" tab that shows all of the "ACTIVE" projects, and the corresponding client names. 

Now I need to highlight the "ACTIVE" cells in that array in blue when it an EXISTING client, or in yellow when it is NEW.

How? Any help would be greatly appreciated!

Any help would be greatly appreciated!

9 Replies


Where does "YES" come into this?

Good catch Hans. Sorry. I meant to say "ACTIVE".


Select the column that you want to color.

I'll assume that the active cell in the selection is in row 1.


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.
Select blue as highlight color.
Click OK, then click OK again.


Repeat these steps, but with the formula


and yellow as highlight color.

Great, Hans. Thank you. That works well for the static array, but if I change more of my data to ACTIVE status, and thereby extend the array, the formatting does not automatically apply. Is it possible to fix that?


Did you select a large enough range to accommodate more results?

@Hans Vogelaar - I did. I had applied the CF to just 4 cells, and it came out just this:

Screenshot 2023-07-18 171450.png

then when tried to change another company on my list to ACTIVE and it came out like this:

Screenshot 2023-07-18 171449.png

 so I tried to add the CF to more cells below and got this...

Screenshot 2023-07-18 171448.png

Could my FILTER be wrong?  

=FILTER('Projects 2023'!A3:B310,'Projects 2023'!A3:A310="ACTIVE")



Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar - Of course. Small workbook attached.

best response confirmed by pcorbitt (Copper Contributor)


Thanks. You'll have to include the Project Type column in the filter.

And the conditional formatting formula should refer to the column on the RESULTS sheet.


You can hide column E on the RESULTS sheet if you wish.

See the attached version.