SOLVED

How to conditionally format a range of not blank cells, then check & format per category

Copper Contributor

Is there a way to conditionally format a range of cells [$C$6:$AT$25 or named range] using formula for the following? -

Firstly, check if cell is not blank [suggested code; NOT(ISBLANK($C$6:$AT$25))].

Then check column A on the same row as that cell and use the following rule:

If CATEGORY1 then format fill yellow,

If CATEGORY2 then format fill blue,

If CATEGORY3 then format fill green,

If CATEGORY4 then format fill red, and so on...

Is this possible in conditional formatting using formula and if so what is the best method?

4 Replies

@Jimmy0600 

Does the attached sample file return the intended result?

Hi @OliverScheurich 

Thanks for looking at this for me.

I've looked at the sample file and found that the formatting only applies to the whole row so if there's one blank cell, then no cells are formatted.  I'm looking for only cells that contain a value in that row to be formatted.

 

As I currently have 23 categories, I would prefer a formula that applies to full range as a whole otherwise, I'll be inputting 23 categories x 19 rows = 437 conditional formatting rules!

Let me know what you can do - many thanks

best response confirmed by HansVogelaar (MVP)
Solution

Hi, @Jimmy0600 

you are welcome. This should work if i correctly understand what you are looking for. The example has 4 categories. For 23 categories (with 23 different colors) 23 conditional formatting rules are required.

conditional formatting.png

Hi @OliverScheurich,

That works perfectly, I've updated my spreadsheet with your formula and very happy with the result.

Thank you for your help, much appreciated.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

Hi, @Jimmy0600 

you are welcome. This should work if i correctly understand what you are looking for. The example has 4 categories. For 23 categories (with 23 different colors) 23 conditional formatting rules are required.

conditional formatting.png

View solution in original post