Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

migrated from superuser.com Jul 1 '11 at 15:27

This question came from our site for computer enthusiasts and power users.

5 Answers 5

This may help you: Google Spreadsheets conditional formatting based on another cell's content

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

share|improve this answer
thx for downvoting without commenting why. –  oleschri Jan 19 '12 at 14:52
Are you for real? The OP already linked that post in the question. –  Veehmot Jul 27 '13 at 5:56
@Veehmot Ô_O That's interesting, as I'm pretty sure this link was not there when I first read the question. I striked out the obsolete link. Thanks for pointing it out :) –  oleschri Aug 1 '13 at 0:10

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

share|improve this answer

This is another non-ideal work-around, but what I did was added a certain number of spaces to the end of the cell needing formatting based on the conditions I wanted, then did conditional formatting based on spaces.

Ex: Column A = First Name, Column C = First Name, Column E = Some Number

So what I want is if E > 50, make column A Blue, If E < 0 make column A Red.

First I type the data that I want in Column A into Column C. {Example: "Fred"}
Column A (row 2) then gets the following formula: =If(E2 > 50, C2 & " ",If(E2 < 0, C2 & " ", C2)) {Example: "Fred", "Fred ", or "Fred "}
Put Conditional formatting on Column A: When Text Contains " " make text blue. When Text Contains " " make text red.
Hide Column C.

Again, not ideal, and you have to use more spaces than the data contains (so if you need to have a space in Column C {example "Firstname Lastname"}, use 2 and 3 spaces for the conditional formatting). But it gets the job done and remains relatively transparent.

share|improve this answer

In new Google Sheets, under Format, Conditional formatting..., for Custom formula is use =A1=1, choose you formatting and for Range use B1:B7 if you want the same as the post now linked to in your question.

share|improve this answer

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.

share|improve this answer
It also doesn't allow for the fact you probably want something else in column C otherwise you would just be using column A. –  Caltor May 9 '13 at 13:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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