SOLVED

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

Copper Contributor

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

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

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

Does the attached sample file return the intended result?

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

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

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

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.

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

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

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

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.