A tricky Conditional Formatting question...

Hi people

I have a slightly complicated question.

Data:
excel question.PNG



I would like to have a conditional formatting that determines in below order:
1. If Settled? column = N...
2. Check if Due Date is older than today
3. Highlight the cell in Due Date in Red if it is older than today
4. Do nothing if its not today but settled = N (donno if this step is done automatically?)

---If Settled? column = Y
1. Just highlight both cell Green


And I would like to format the whole column at the same time, wondering if the conditional formatting can do them all at the same time, because I am unsure if there's a function to determine 'if the cell on the right'

Thank you!

That should be straightforward, if we knew the columns involved.

Select B2

Conditional Formatting
New Rule
Use a formula to determine...

=(AND(A1="N",B1<TODAY())

Format as required
Use Format painter (paintbrush icon) to copy to other cells

Select A2:B7, goto Format>Conditional formatting..., New Rule, select Use formula to determine which cells to format, enter this formula,

Formula:

Please Login or Register  to view this content.

and format as required.

Select B2:B7, goto Format>Conditional formatting..., New Rule, select Use formula to determine which cells to format, enter this formula,

Formula:

Please Login or Register  to view this content.

and format as required.

Not sure about your conditions but it sounds like what you want is a SUMIFS (or SUMIF if there's only one condition) formula.

For example, if you wanted to sum column C if column A is Y you could use this.

Formula:

Please Login or Register  to view this content.