Help with conditional highlighting

Here is a relatively simple way to highlight a whole row if you can live without a bunch of different color codes.

This one highlights rows where A is unchecked and the date in B is tomorrow or before.

The trick (which Badunit once illustrated in another thread) is to add an extra column with logic that inserts an unusual character (I used ~ but any will do that will not appear at the end of text anywhere else in your table) there for rows that you do NOT want to be highlighted and leaves the cell empty if conditions are such that you DO want highlighting.

In this example the formula in L2, filled down, is:

IF(AND(NOT(A2),AND(NOT(B2=""),B2<TODAY()+2)),"","~")

If the cell in A is not checked and B is blank or a date less than the day after tomorrow then it inserts the unusual character.

Then you can select the body cells, in this case A2:K11 and apply Conditional Formatting like this:

Note the $ before the L.

To get that you click the box-and-arrow icon:

Then, in this example, click cell L2 in the table, giving this:

Then click the downward triangle in the oval address token and check 'Preserve Column'.

Then hide column L if you want.

This won't work for a fancy scheme with lots of logic and different colors. But maybe simple will be more practical anyway.😀

SG

Hi petro50,

Conditional highlighting in Numbers is controlled by a set of rules applied to each cell to be highlighted.

The rules compare the value in the cell with a fixed value, written into the rule, or to the value in another cell.

There are two ways to handle CH in your case. Both involve a second table.

1 provide a 'partner' table or an extension to your existing table in which each cell is partnered with the cell in the same position on the table to which the ch rules will apply.

OR

2 provide a second, single column, table with rows matching the height of the corresponding rows in the main table and the width of the single column matching the width of the main table.

A variation of the second method would be needed as the highlighting there is of part of a row rather than the full row.

"BOOKED LOADS"

Some clarification is needed here:

"For table one I have a checkbox column in A, what I want is , have the whole row filled in red if the checkbox is unchecked , but

I only want this (the row filled red) to happen if there's is nothing in column B ,

so if there's no data in column B I do not want the conditional highlighting. S"

Highlighting of a row is controlled by the states of the two cells in columns A and B of each row

Highlighting the row is to be done using 'red fill' to colour the cells in this row.

There are 10 possible states for the two control cells.

The first cell (A2) contains either 'true' (checked) or 'false' (unchecked)

The second cell (B2) will have no content (blank) or content fitting one of the types listed below:

"" (blank)

A date more than one day after Today

Tomorrow's date

Todays date

A date before today

Please copy the list below, and paste it into your reply, then select each line describing a state that is cause red highlighting of its row in the table "BOOKED LOADS"

A false,  B "" (blank)

A false,  B a date more than one day after Today

A false,  B Tomorrow's date

A false,  B Todays date

A false,  B A date before today

A  true,  B "" (blank)

A  true,  B a date more than one day after Today

A  true,  B Tomorrow's date

A  true,  B Todays date

A  true,  B A date before today

"VLAD"

Working on this one, & will have a reply later today.

Regards,

Barry

Certainly.

Running into some difficulties with highlighting cells in a full row where there are empty cells, and making distinct comparison values for dates to have three of more states (eg: red, non, red, yellow or red, non, red, green.

Regards,

Barry

Haven't said that, but also haven't found a way to do it.

Might be possible with a script or a script based Automator /Service, but neither of those would be self-activating. You'd need user intervention—a menu choice or a keystroke combination—each time you wanted the highlighting to change.

(W)hy won't adding another column that I can hide later prevent me from doing multi color labeling? so another column will check for checkbox if it is true or false, then highlight just the date alone, not whole row since for table 2 that suits it better, not the whole row."

HI petro50,

I'm not sure what you are saying and asking here.

To highlight the cell containing a date, you need to have a cell (let's call it Q5) containing a date that can be compared with the date in the cell to be highlighted let's call this cell C5).

To have the highlighting of the date cell depend on the state of a checkbox (B5), you need a formula in Q5 that sets Q5 to a Date and time value determined by the state of the checkbox that can be compared to the value in C5

A possible formula for Q5 could be: IF(B5,C5,C5-1)

In English: 'if cell B5 is checked, set the date in this cell (Q5) to the date in C5, otherwise, set the date in this cell to one day before the date in C5.

If the date cel lis to be highlighted only when the checkbox is false (unchecked), the conditional highlighting rule for C5 (containing the date to be highlighted) would be:

If Date IS After Q5 Red Fill

In English: If the date in 'this cell' ( C5 ) is after the date in cell Q5, then set the colour fill of this cell to RED.

Regards,

Barry

petro50 wrote:

highlight just the date alone, not whole row since for table 2 that suits it better

Yes, of course, you could do multicolor highlighting on the date cells and just one color highlighting on other cells in the row. Just use the technique I described above for the "whole row" and have separate highlighting rules for the date cells that compare those cells to cells in a different column or columns.

SG

petro50 wrote:

is there any way for me to make a row bold based on a checkbox?

Yes, you can try something like this:

The formula in N2, filled down:

=IF(NOT(M2),"~","")

Conditional Highlighting rule would be something like this:

SG

Making a row's text bold is the same task as setting RED fill on all the cells of that row. The only difference is in specifying what to do when the condition(s) are met.

Rule: (if) text is (Q5) then RED fill

Rule: (if) text is (Q5) then bold text

Same condition, with a different 'do this' highlighting outcomes.

SG has provided a means of filling a whole row with red.

The same condition setup with a different highlight (bold text) will bold the text in the same row. Using "custom", the last item on the CH outcomes menu, you can set both bold and red fill as the highlightin to be placed on meeting the condition.

Regards,

Barry