highlight row based on checkmark

Here is another method. It requires 4 new columns (that you can hide later), one for each highlighting rule/color.

Formula in cell L2 =IF(H2,CHAR(10000),"")

Fill to the right to do the other three columns then fill down to complete the columns.

To make the rules,

1. Select cells A2-G2
2. Create the first rule as shown in the screenshot.
3. In the box where you see $L2, you will click on the green oval then click on cell L2.
4. Edit the L2 so it preserves the column as $L2. This is required for it to work properly.
5. Repeat for the rest of the rules.
6. With A2-G2 selected, copy the style using Copy Style in the Format menu
7. Select the rest of the cells in columns A-G and use Paste Style to paste the rules to them.

You might want to use "custom style" highlighting and chose the fill color manually vs using the built in color fills in my example. The built-in fills uses colors that are not the same as the pallette of colors you can choose from for formatting cell backgrounds, which you will be doing to columns H-K. If you use the built-in fill colors, it will be hard to match them when coloring in columns H-K.

Hi Gary,

Yes it is!

Conditional highlighting rules compare the contents of the cell to be highlighted with a fixed value, written into the rule, or with the value inserted in another cell. Each of the cells you want to highlight contains a different value, so the first comparison—with a fixed value— won't be very useful here.

That means each cell to be highlighted needs a 'partner cell' to contain a value to be compared with the value in the cell to be highlighted'.

You have four different highlights to be used, so you will need four different comparisons to match.

Although some of the cells to be highlighted contain numbers, these can be treated as text for the comparisons, provided the comparisons look at enough characters to differentiate between the comparisons for each cell.

The four comparisons I've used, in the order they are applied, is this:

1. Is the same as…
2. Begins with…
3. Ends with…
4. Contains…

Create a duplicate of your table, Delete columns H to of the duplicate, then enter the formula shown below the tables in cell A2, of the duplicate, and fill that formula into all cells in columns A to E of the duplicate table. I've left columns F and G as these will not contain values able to match any of four different comparisons.)

(Hint: Don't convert column A to a header column as I did. Set as an ordinary data column, you'll be able to Fill the formula across it from A2 to all other data columns in the table. If a is a Header table, you'll be able to fill the formula down column A, but will then need to re-enter it in to B2 (changing the cell references to B2 of the original table) before filling it to the rest of the cells in the table.)

Table 1 is the original, Table 1-1 is the duplicate, containing the partner cells.

Regards,

Barry