Conditional formatting based on another cell

[michaelbr]

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?

[anon73440385]

Hello

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: ConditionalMaleFemale.ods and test using the dropdown in cell A1. To see how it is defined use Format -> Conditional -> Manage ....

Hope that helps.

[michaelbr]

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

[michaelbr]

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

[dbophxlip2017]

I have a question about this, how would you format a row based on a value of a cell in that row
Example:
$A$1=X then black out the entire row
same with $B$1 and etc