Ask Your Question
0

Set cell background color based on adjacent cell value [closed]

asked 2014-03-17 17:30:54 +0200

jheath gravatar image

I have a finance journal in Calc and I would like to use (I think) conditional formatting to set the background color of a cell, but not based on its own value, but the value of an adjacent cell, in fact when it matches a certain account name. So if I have FIN: Bank in column 1, I would like to apply the cell format Highlighted in column 2. Any other contents in column 1 and column 2 would just have the Default formatting. Is that possible?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:22:50.643369

1 Answer

Sort by » oldest newest most voted
1

answered 2014-03-17 21:09:03 +0200

kumiponi gravatar image

Yes it is! When you create a conditional formatting rule for column B, select "Formula is" as the condition, and type A1="some string" as the formula (in the adjacent text box). Then select the style you want to apply.

edit flag offensive delete link more

Comments

Thanks for that - that does seem to work. I could not for the life of me figure that out from the help file. Maybe the help file should add an example of "Formula is", is it certainly isn't intuitively obvious...

jheath gravatar imagejheath ( 2014-03-18 09:27:24 +0200 )edit

Yes, think of the "formula" as a condition in an IF statement. If it returns boolean TRUE, then the formatting applies. "A1" in my example has to be adjusted if the first cell in the conditional formatting range isn't B1; the references in the formula are appropriate to the first cell in the range and are implicitly adjusted for the rest.

If you want, you can also embed "conditional formatting" in the cell formulas themselves with the STYLE and CURRENT functions.

kumiponi gravatar imagekumiponi ( 2014-03-18 13:38:44 +0200 )edit

Thanks again for that insight. I assume that putting the formatting in the cell formulas would only work if your cell has a formula (and then you "add in" the conditional formatting). If the user can type a number into that cell, that would wipe out any formula that was there, correct? So I think I need to use the conditional formatting from the menu.

jheath gravatar imagejheath ( 2014-03-20 10:26:12 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-17 17:30:54 +0200

Seen: 34,526 times

Last updated: Mar 17 '14