Google Sheets: How to fill cells in colour depending on the data indicated on another sheet

Question

This is quite a hard question to explain. Here is how it looks.

I have a script on Google Sheets that exports data to Google Form quiz. There are 500+ questions in quiz, properly filled with that script. To put the Right answers (Key answers) in Google Form I need to fill right cells in Google Sheet in green colour, then the script will do its job.

Key answers are filled in column M. Digits: 1=(right key answer) in column 1 (I), 2=J, 3=K and 4=L.

Can I use a formula or a macro that will check column M and fill in green colour Right answer cells on the same row?

Answer 1

If I'm understanding you correctly, this can be done with a custom Conditional Format formula. Do the following:

1. Click on cell I4. Select from the menu Format > Conditional Formatting

2. When the CF window opens, the "Apply to range" field will say I4. Change it to I4:L.

3. Under "Format rules / Format cells if..." click the dropdown arrow and select the last option: "Custom formula is"

4. In the blank field that appears below that (it will show a "Value or formula" placeholder), enter the following custom formula: =(ISNUMBER($M4))*(COLUMN(I4)=COLUMN($I4)+($M4-1))

5. In the "Formatting style" panel, click the paint bucket icon and select the background color of your choice.

6. Click the "Done" button and close the Conditional Formatting dialog window.