0

I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements:

=IF(A1="5","Well Done!", "")

In this scenario, if the child enters the correct answer of '5', they are told it is correct. I would like to do the same thing with color, where they enter red and are told it is correct. Is this possible?

0

Nick, I might be missing something, but can't you just use

=IF(A1="red","Well Done!", "")
  • Hi Alec, I'm talking about them changing the actual color of the cell using the fill function. – Nick Apr 23 at 11:02
  • How about if you use data > data validation > list of items "red, orange, yellow, green, blue, etc", then you can compare the colour word in your formula and you can use conditional formatting to change the cell colours however you want. Does that work? – Alec McCartney Apr 23 at 11:23
  • I need to know if a formula can look at a colour in a cell and act on that. The child would colour a certain cell a certain colour, and if they did it correct a text would appear in another cell to tell them they were right. Would the data validation be able to do this? As far as I can see it just looks at the text and not the colour. – Nick Apr 23 at 11:27
  • Hi Nick, no, data validation would not work that way. user0's script method below looks good. – Alec McCartney Apr 23 at 12:28
0
  • add this script to your sheet:

    function GETHEX(input) { 
    return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getBackgrounds(); }

  • cell E20: =GETHEX("B20")

    0

  • cell C20: =IF(E20="#ff0000", "well done", "you failed!")

  • hide column E

    0

  • That's for that! It definitely looks on the right track, but I cannot get it to work. The imgur pictures are blocked on my network so I will have a look when I get home to see if that helps. When I try, the E20 shows #ffffff and the C20 shows 'you failed'. I am aiming for it to show you win when I change to the correct colour. – Nick Apr 23 at 14:02
  • Thanks again for this, it was a lot clearer with the pictures when I tried at home. Unfortunately I wasn't able to get it working like I wanted. I am aiming for immediate feedback where the red colour gives 'correct' and any other colour gives 'incorrect'. When added the script and ran it, it only seemed to show a difference when I showed and hid the E column. I was also receiving an error message on line 2 of the code. It might be that I need to better understand how scripts work on a Google Sheet, in which case I will do some research into it. Thanks for getting me started. – Nick Apr 24 at 7:16
0

0

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.