Change the formatting of a cell based on the value of another cell

There is a way to highlight an entire row without too much trouble.

The idea is to set up an extra column that can later be hidden. In that column you put a formula that places a special character of certain conditions are not met, otherwise nothing. Then you base your Conditional Formatting on a comparison to cells in that column.

Here is a simple example:

The formula in E2, filled down, is:

=IF(NOT(D2="Request"),"~","")

The Conditional Highlight rule is shown in the screenshot. To enter it you select the range, in this example A2:A5, Add a Rule, choose Text ends with, click the token in the box, then E2, and make sure to check Preserve Column.

This works for one highlighting color. Not sure how it could be extended to multiple colors as in your screenshot, though.

SG

Hi RemyDemarest

Here is one way. Duplicate the table and apply Conditional Highlighting. We will slide that table underneath Table 1.

We need a Lookup table (yes, the colours of the rainbow as an example).

Formula in Table 1-1 A2: INDEX(Lookup::$B,MATCH(Table 1::$D2,Lookup::$A,0))

Edit: I see that your region uses semicolons

Fill down and fill right.

Now to colour the cells in Table 1-1: (text colour must match the cell fill to become hidden).

With Table1-1 selected, change the text colour of the table title to white. Then the title will not show, but it will occupy the same space as the title of Table 1.

Format Panel > Arrange > Back.

Now slide Table 1-1 underneath Table 1:

Not ideal; the cell borders do not match.

Perhaps another user will step in to help.

Regards,

Ian.

Hi RemyDemarest

Here is one way. Duplicate the table and apply Conditional Highlighting. We will slide that table underneath Table 1.

We need a Lookup table (yes, the colours of the rainbow as an example).

Formula in Table 1-1 A2: INDEX(Lookup::$B,MATCH(Table 1::$D2,Lookup::$A,0))

Edit: I see that your region uses semicolons.

INDEX(Lookup::$B;MATCH(Table 1::$D2;Lookup::$A;0))

Fill down and fill right.

Now to colour the cells in Table 1-1: (text colour must match the cell fill to become hidden).

With Table1-1 selected, change the text colour of the table title to white. Then the title will not show, but it will occupy the same space as the title of Table 1.

Format Panel > Arrange > Back.

Now slide Table 1-1 underneath Table 1:

Not ideal; the cell borders do not match.

Perhaps another user will step in to help.

Regards,

Ian.

It is very difficult / nearly impossible to format a cell based on other cells.

Could you post a screenshot of your table?

If you have not too many columns it could be doable.

Please show a few examples on how it should look and what data you enter.

Unfortunately Numbers does not offer the same options as Excel does.

Ralf