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

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

jheath
142 ●13 ●16 ●25

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?

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

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

kumiponi

216 ●1 ●5 ●8

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

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 ( 2014-03-18 09:27:24 +0100 )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 ( 2014-03-18 13:38:44 +0100 )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 ( 2014-03-20 10:26:12 +0100 )edit

add a comment

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

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

Comments

Question Tools

Stats

Related questions

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

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

Comments

Question Tools

Stats

Related questions

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

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