For Windows version.
Microsoft 365 Excel (Version 2004(Build 12730.20270)
*Note the version is not the year - this is the 365 version in year 2020
You need to create 2 rules (one for each colour)
Home Tab -> Styles Group -> Conditional formatting -> Manage rules
*Alternatively just type Conditional formatting in the search bar at the top
You can do it like the following:
Select the cells that should be formatted.
Create a new formatting rule
On "Select a Rule Type", select "Format only cells that contain"
On "Format only cells with", select equal to and set it to TRUE. Choose the color and font under "Format".
Use this formula as New Rule:
Apply an appropriate cell format.
To justify the formula, purposely I've put 25 in cell B152, since E152 has P.
You may adjust cell references in the formula as needed, also the marks to check.
In the conditional formatting in, say, cell A11, use this rule
Note the rule looks at the row directly above. The 3,3 parameter in AGGREGATE in plain English is
show the COUNTA of visible cells.
When A10 is hidden, the rule is TRUE and then you can set the conditional formatting in A11 on that. By the way, this works only if cell ...