Search code examples
google-sheetsgs-conditional-formatting

Conditional formatting on Multiple Rows based on cell value

In my Google Form, I have custom formatting setup for my dropdown lists that will highlight the entire row based on the value chosen. 

Custom formula is
=$B3="Highlight: 1 Row"

But how can I highlight multiple rows if I wanted to?

Google Sheet Example: Highlight Multiple Rows
Screenshot Example:

enter image description here


UPDATE (01-26-2017)

The purpose of this "multi row" feature will be used inside a more robust spreadsheet that generates Google Forms from a Google Sheet. For this particular use case, there are many different "dropdowns" for the user to choose from that control where the user can type. This can quickly become hard to navigate the longer your sheet becomes. The best way it seems to handle this is through conditional formatting.

Solution

  • Use this custom formula

    =$B$3="Highlight: 2 Row"

    Make sure the range you want this rule to apply to, is two rows long

    e.g: B3:Z4

    Now when "Highlight: 2 Row" is entered in B3 two rows should be highlighted.

    UPDATE:

    Below is a google script that should work

    function onEdit(e) {
var sh, colors, rows, rowsBefore;
sh = e.source.getActiveSheet();
colors = ["#fff2cc", "#fce5cd", "#d9ead3", "#cfe2f3"];
if (sh.getName() !== 'Example' || e.range.columnStart !== 2 || e.range.rowStart < 8 || typeof e.value == 'object') return;
rowsBefore = e.oldValue ? e.oldValue.replace(/\D/g, '') : 1;
rows = e.value.replace(/\D/g, '');
sh.getRange(e.range.rowStart, 4, rowsBefore, sh.getLastColumn()).setBackground("white")
sh.getRange(e.range.rowStart, 4, rows, sh.getLastColumn()).setBackground(colors[rows - 1])
}

    Change sheet name (and range) to suit. Note that this script is on a simple onEdit trigger: it will be fired when an edit is done in col B of the sheet 'Example'. Do not try to run the script by clicking the 'play' button in the script editor as it will return an error. See if that helps ?