Conditional Formatting based on values of table

0
votes

I would like to turn black a cell if on the table any of the rows match this condition:

Table : A1:G100
Cell to format J1

Condition that I would like to to implement:

IF(AND(A1=0;OR(B1="bob";B1="jack"))

turn the cell J1 black.

Example

I used the Excel conditional formatting function and I got the result applying the formula only to the first row of my table. How can I format the cell based on the repetition of the formula for each column of the table?

3
excelconditional-formatting
"I used the Excel conditional formatting function" isn't a good enough description. What exactly did you enter? Because this should work. If you apply the rule to the entire desired range, any relative cell references should be updated as Excel progresses through the range (relative to the top-left), leading to the correct results. – underscore_d
I insert this formula on the conditional formatting tool =AND(A1=0;OR(B1="bob";B1="jack")) I wrote IF on the post in order to make it easier to understand which is my final objective – Magnicco
Edit your post to include the exact info on what you did. Not some other thing that you think makes it easier to understand... How does showing something different from what you did aid understanding? – underscore_d
It seems that Tom Sharpe understood it – Magnicco

3 Answers

1
votes

Suggest a custom formula for J1 of

=COUNTIFS(A:A,0,B:B,"bob")+COUNTIFS(A:A,0,B:B,"jack")

and choose black fill colour.

1
votes

There is no need to add IF, the condition is already an IF:

add ,A2=0 at the back only if you want to color Number 0 (column A) as well - just change the color to J1 in conditional formatting fill format

=OR(AND($A2=0,OR(A2="jack",A2="bob")),A2=0)

enter image description here

0
votes

It's an intense formula but if you are looking for an one formula answer strictly for the conditional formatting formula and impacting nothing else, try the following. your formula in the conditional formatting formula should be

=SUMPRODUCT((A:A=0)*(B:B="jack"))+SUMPRODUCT((A:A=0)*(B:B="bob"))>0

The reason for the two separate sumproduct formulas is to capture "jack" and "bob" instances as an inclusive OR which is triggered by the sum of the two being greater than zero (at least 1 instance of "0 and jack" OR "0 and bob" must be true).

Please use this answer ONLY if you have a special purpose for your J1 cell and you cannot afford a logic block (usually on a separate sheet) to colour your cell. Otherwise please refer to @Tom Sharpe's answer. His formula could be contained anywhere and the conditional formatting simply look if that cell is greater than 0.