6440 Views 5 Replies Latest reply: Jun 15, 2010 8:58 PM by Barry
I'm not sure what you mean… I can set the criteria of "Equal to" TRUE to change the cell. The other cells in the row have text, dates, numbers, different data types. I want the entire row background to change color if that single cell becomes TRUE. I do not know how to tell the other cells to change color based on the value of a different cell.
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...
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.
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.