Conditionally formatting a cell based on another cell's value in Google Spreadsheet

Question

I did some searching on this and came across this post

However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that post, the formula would be like so:

=setcolor( if(A1=1,true,false), RGB(255,255,0) )

How can I create a formula that does this? Thanks in advance.

Answer 1

This may help you: Google spreadsheets conditional formatting

How to enter your Apps Script into your spreadsheet: http://code.google.com/intl/de/googleapps/appsscript/guide.html

Answer 2

It is not possible to use formulas for conditional formatting based on this Google Groups Discussion.

Answer 3

This is how I do it.. My example i want column C to turn black when column A says "Advert"

I thought of a way to make the columns in C in Google Docs turn black automatically when you type "ADVERT" in Column A. This has stumped me in the past, as unlike Excel, Google Docs doesn't allow color alteration of cells based on formulas from another cell, only its own using conditional formatting.

However, by using the conditional formatting on desired cell (Column C in this case)

is equal to ADVERT then background = BLACK.  
# (Translation: if cell C has the word ADVERT, then paint that Cell in C black) 

so far not much use as we type ADVERT in A, but bear with me.

Then by using this formula in column C

if (A41="ADVERT","ADVERT","") on that column 
#translation: if Cell A41 says ADVERT then print the word Advert in this column, or if not print nothing. 

Then the word "Advert" will appear in column C if Column A has that word. That added to the single cell conditional formatting we added at the start of this geek fest, it will see the word ADVERT in its cell and then paint it black (the word advert will not show as its black on black. Doesn't save us much time, but its nice to know such things are possible.