Ask Your Question

Conditional formatting based on another cell

asked 2020-07-09 16:30:42 +0100

michaelbr gravatar image

updated 2020-09-21 23:11:41 +0100

Alex Kemp gravatar image

I have a spreadsheet with 1 cell A1 is either Male or Female, and 2 columns, A5 to A10 and B5 to B10, when the A1 is Male, then I'd like to highlight A5:A10 to bold and background color to yellow, and when A1 is Female, I'd like to highlight B5:B10 to bold and background color to yellow, question

1) how can I accomplish this? I've tried to find tutorial, only found those with direct formatting.

2) it seems the way to do it is to create a "highlight" style, when I switch Male to Female, do I need to clear the formatting of previously selected column or when the new condition applies, the previous one will be gone?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-07-09 17:24:54 +0100

Opaque gravatar image

updated 2020-07-09 18:14:05 +0100


you need to define 2 Conditional Formatting

  • One for range A5:A10 using Condition 1: Formula is: $A$1="Male" and assign a cell style (e.g. Male)
  • One for range B5:B10 using Condition 1: Formula is: $A$1="Female" and assign a cell style (e.g Female)

The cell styles Male and Female should be created in advance using F11 or Styles -> Manage Styles. Doing so the are available when you define the Conditional Format (The names of the styles are of course your own choice)

(Since you don't in fact distinguish the style Male and Female you may choose one only, but I would not recommend to do so, since you may want to have different colors or fonts in the future and having 2 styles will support a smooth change)

Please check the following file: C:\fakepath\ConditionalMaleFemale.ods and test using the dropdown in cell A1. To see how it is defined use Format -> Conditional -> Manage ....

Hope that helps.

edit flag offensive delete link more


Thanks Opaque for your prompt reply, let me sometime to understand it and check it, I'll post back with my feedback.

michaelbr gravatar imagemichaelbr ( 2020-07-09 20:42:24 +0100 )edit

Thanks so much for your help and explanation, it's much appreciated.

michaelbr gravatar imagemichaelbr ( 2020-07-10 14:35:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-09 16:30:42 +0100

Seen: 830 times

Last updated: Jul 09 '20