Conditional Format an entire row based on one cell's value?

Sterling,

You can format the entire row at once and every cell in that row will behave the same relative to the criteria.

Jerry

To achieve this requirement, I would use the good old trick :

(1) define the table's own background to none
(2) I will build a table of the same size,
with the same number of rows.
The number of columns may be smaller if you enlarge these column to fit the first table's width.
(3) In the cells of this auxiliary table, insert formulas grabbing the value of the checkboxes.
Define a conditional format so that their background becomes red as well as the text when the checkbox is checked (cell's content's will be TRUE)
or that background is white, text color is white when the box is uncheked (FALSE).

(4) move this aux table exactly behind the first one.

Yvan KOENIG (VALLAURIS, France) mardi 15 juin 2010 22:13:53

I'm not sure if you are done giving me specifics that will affect the solution or not. Assuming that we know all we need to know, I think that the most elegant solution is one I think of as the controlled mirror table method. In a table of the same dimensions as the original and hidden on another sheet, you mimic the main table, if the box is checked for the row. If the box is not checked, you put something else in the cell. Then in the main table the conditional format checks the secondary table for an equality and formats the cells accordingly. This method allows any kind of formatting change that you could possibly want. It has the advantage over the method that Yvan has explained in that you can adjust row heights and column widths without worrying about the effect on the second table's alignment. The big problem with the controlled mirror table is that it is tedious to set up since you do have to visit each and every cell to point its conditional format toward the mirror table's corresponding cell. We don't have the ability to specify a relative address for the comparison cell's location. Maybe in the next version of Numbers...

Jerry

Yvans method is one I have described in the past, except that I would use a single column table, with the same number (and height) of rows as the overlying main table and the width of the column set to the full width of the main table.

The easiest way to get the second table is to select (in the Sheets list) the original, then press command-D to duplicate it.
Drag the duplicate to a position directly below the original, select column A and increase its width to the full width of the original, then select and delete the rest of the columns from the duplicate.

Formula for the underlying table: =Table 1:A
Where A is the column containing the checkboxes.
Enter formula and fill down the column.
Set Text colour to White.

Conditional format:
Equal to: TRUE
Set text colour and fill colour to same highlight colour.


Main table: Select all cells.
Click the Fill colour well to display the colors palette.
Click the Colors button to open the Colors inspector.
In the Colors inspector, set Opacity to 0

Select the secondary table by clicking its icon in the Sheets list.
Go Arrange > Move Back

Use the Arrow keys to move it behind and align it with the main table.

Regards,
Barry