Conditional Formatting when I've 1 TRUE i need to color also 2 FALSE

Maybe this:

Formula:

Please Login or Register  to view this content.

Woow!! TMS Thank You so much, I will never understand it but it works perfectly
SolVED

EDIT Also thanks to Pete, it works fine either

You're welcome. Thanks for the rep.

You have a single OR formula with three possible conditions. If any condition is TRUE, then the formula returns TRUE. The first condition is a simple comparison: is Cell A1 TRUE? As this condition is dragged down, it will refer to cells A2, A3, A4 and so on. For the sake of argument, let's think about Cell A3; if A3 is TRUE, then the result is TRUE. But we also need to check the previous two cells, A2 and A1 to see if either of them are TRUE.

In order to check the previous cells, we are using INDEX. The INDEX function returns a cell from a range depending on a "pointer". For the second condition, we are using ROW()-1, and for the third, we are using ROW()-2. The ROW() function returns the row number for the current cell. Hence, ROW()-1 refers to the previous cell row, and ROW()-2 refers to the cell row prior to that.

In this case, on row 3, INDEX(A:A, ROW()-1) refers to cell A2 and INDEX(A:A, ROW()-2) refers to cell A1. So, effectively, we are checking if cells A3, A2 or A1 are TRUE and, if any one of them is, the OR formula will return TRUE.

That's OK for cell A3 down if we drag the formula down, as we refer to cells A4, A3, A2, then A5, A4, A3, and so on. BUT, if we drag up from row 3, we are going to get an error because the "pointer" will go zero and then go negative. That's why we use IFERROR to cater for the error generated and we make that return FALSE.

Surprisingly, at least to me, I didn't need the IFERROR on the middle condition, but it wouldn't hurt.

So, belt and braces,

Formula:

Please Login or Register  to view this content.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome.