Ask Your Question

Libre Office Calc Column Compare

asked 2018-10-20 16:54:10 +0100

bfpa40 gravatar image

How would I take the Values of A1 - A6 and compare them to the Values in Columns B1-F6 and any values that match change the background color to limegreen of only those matching cells?

Thanks in advance -Brian-

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-20 18:04:04 +0100

updated 2018-10-20 18:06:13 +0100

Hello, @bfpa40. Go to Format -> Styles -> New Style... and create style with your preferred name, lets call it LimeStyle Then go to Format -> Styles -> Manage Style, right click on LimeStyle and click Modify. Select Font Effects tab and choose the font and other styling effects you want. Then select A1:A6 range, go to Format -> Conditional Formatting - > Condition... and under Condition 1 select Formula is, copy this formula in the text box SUMPRODUCT($B$1:$F$6;A1=$B$1:$F$6) and under Apply Style select LimeStyle and click Ok.

edit flag offensive delete link more


I think the original question was rather about the value in column A matches any of the values in the same row of columns B to F, so the formula should be SUMPRODUCT(A1=B1:F1)>0

[I missed the 2D range earlier so deleted the previous comment]

erAck gravatar imageerAck ( 2018-10-20 18:49:37 +0100 )edit

Deleted mine as well. Yes, if the question is about matching value in the same row, SUMPRODUCT(A1=B1:F1)>0 is correct choice. I thought that @bfpa40 needs to match values in A1:A6 in the whole range. Read the question several times - still not sure what is the correct option. Anyway, TS now has two options to chose from :)

SM_Riga gravatar imageSM_Riga ( 2018-10-20 19:09:28 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-20 16:54:10 +0100

Seen: 35 times

Last updated: Oct 20 '18