Excel Conditional Formatting with Multiple Conditions

I am having an issue with the conditional formatting rules. Forewarning, I am new to learning excel formulas so I have read through a lot of documentation but haven’t been able to solve it.

I have a sheet that has the following values:

Let’s call A1:A3 the weekly areas.

A1 Cell - NC

A2 Cell - TX

A3 Cell - CA

Let’s call B1:B3 the picked areas and B4 is the concatenation.

B1 Cell - TX

B2 Cell - CA

B3 Cell - NM

B4 Cell - =CONCATENATE(A1:A3)

The B4 output for this week for be ‘NCTXCA’. I thought about using =JOIN(“,”,A1:A3) which would produce ‘NC,TX,CA’ but as the weekly area cells change based off of the weeks locations, when cells A1:A3 are blank… Cell B4 is not blank and shows ‘,,’ in the cell. I assume this is because it is still accounting for the separation between Cell A1 and A2 and A2 from A3. I’d like to get this fixed as it eliminates the possibility that the text could produce another potential area. For example, in ‘ NCTXCA’ you can see that CT could be another potential area that could mess up the formatting. That is a secondary question.

On to the main conditional formatting question:

On the sheet I have the following conditional formatting rules in order:

*Cells B1:B3 are blank

Range - B1:B3

Format - is empty

Style - Background color is Blue

*Cell B1:B3 text are in B4

Range - B1:B3

Format - =IFERROR(FIND(B1, $B$4),FALSE)

Style - Background color is Green

*Cell B1:B3 text are not in B4

Range - B1:B3

Format - =IFERROR(FIND(B1, $B$4),TRUE)

Style - Background color is Red

*** QUESTION ***

This does seem to work for checking if the B1:B3 cells are blank and making them Blue, if the text is within B4 and making it Green, if the text is not within B4 and making it Red; however, as the weekly areas change the A1:A3 cells may be blank and not contain any areas. With the current formatting, when B4 is blank (because there aren’t any areas assigned in A1:A3) the B1:B3 cells are red because the text in those cells do not match the empty B4 cell. Is there a way to format the cells so if B4 is blank and the B1:B3 cells contain text they are Blue as well (because they are neither right nor wrong)?

I have tried using ISBLANK(B4) and I can’t seem to figure out the formula I’d need to use for the formatting.

Thanks in advance!

Your main question seems to be: "Is there a way to format the cells so if B4 is blank and the B1:B3 cells contain text they are Blue as well (because they are neither right nor wrong)?"

Ignoring all the other formatting rules you have, a format condition for B1:B3 when they contain text and B4 is blank is this:

=AND(B1<>"", $B$4="")

Apply that to B1:B3. That turns those cells blue under the condition you've specified (first 2 pictures below). The AND() requires that both conditions hold simultaneously, which you need here. I'll let you figure out how to fit it in with all the other conditions.

With conditional formatting, all you need is a formula that returns TRUE when you want to turn on a format, and FALSE under any other condition. I often build the formula in a cell, then paste it into the conditional format dialog once I've got it right (below).

=ISBLANK($B$4) is the correct formula. Check that you have used absolute references (the dollar signs). And check that the ISBLANK rule is above your red rule in the Rules Manager. The rules are applied from top to bottom, so the first rule that satisfies the criteria will be used.

To your second question, JOIN is not a worksheet formula, I assume you mean TEXTJOIN? The second argument for TEXTJOIN is ignore_empty. If you use TRUE it will ignore your empty cells.