Excel 2016 Conditional Formatting

0
votes

Can anybody help with the below :

I want two conditions to be fulfilled in one single cell. For example

Conditional Cell is A2, what i need is when A2 > A1 the text should turn red and also at the same time if A3 = 100 (or any other cell) the background color of A2 should turn green. I need these two conditions working simultaneously in A2 cell. I am using Excel 2016.

Early reply would be highly appreciated.

1
excel
What if A2 is not greater than A1, BUT A3 = 100? That is a possible scenario you have not described. You need to look into conditional formatting rules that use formulas. - teylyn
What do you mean with "A3 = 100 (or any other cell)" -- If any other cell in the spreadsheet is = 100? Really? Or do you want to narrow that down a bit? - teylyn

1 Answers

1
votes

Font and fill cannot be set with separate rules. A rule will always set both. Therefore, you will need several rules to cover all the possibilities with the options in the two cells

  1. A2 > A1 and at the same time A3 = 100 -> Red font with green fill
  2. A2 <= A1 and at the same time A3 = 100 -> Black font with green fill
  3. A2 > A1 and at the same time A3 <> 100 -> Red font with no fill
  4. A2 <= A1 and at the same time A3 <> 100 -> Black font with no fill

Number 4 is no special formatting at all, so you need three rules for the other three cases. Select A2 and create rules that use formulas

Rule for number 1: =and(A2<=A1,A3=100)

Rule for number 2: =and(A2>A1,A3<>100)

Rule for number 3: =and(A2<=A1,A3<>100)

Set the formats accordingly.