Highlight cells on the results of calculated & conditionally formatted cell

NOTE: I’m a novice and this wonderful spreadsheet has been developed with the help of members from this forum, so thank you all very much!

Using Excel in Office 365

I wish to highlight names the same as the highlighted Subtotal cell.

-----------

I have a Lotto spreadsheet where drawn lotto numbers are entered in F18:L28

Individual Names are in column F32 and their Lotto Numbers in: G32: P81

The person(s) lotto numbers are subtotaled in: Q32 to Q81

=SUMPRODUCT(--(COUNTIF($G$18:$M$28,$G32:$P32)>0))

I’ve also applied Conditional formatting to highlight the subtotals to quickly identify those with the most drawn numbers and to ensure duplicate lotto numbers are not counted and grey-out and strike through drawn numbers:

Applies to: =$F$32:$O$81

Formula: =COUNTIF($F$18:$L$28,F32)>=1

Oops, Person 8 should be highlighted vice Person 7.

Again, this currently works well, but I would also like to highlight the person(s) name, the same colour as the subtotal field.

Sorry for any confusion in my description, however, I look forward to some guidance.

Cheers,

excel

office 365

Lorenzo
Aug 13, 2022
DBrydon

- No reason to change the 1st rule (filling grey) as it doesn't apply to your Subtotal col. Keep it is was
- 2nd rule (filling red) can be adapted to Apply to F32:F81
- 3rd rule (Graded Color Scale), no way. Such rule apply to a single column only and that column must contain numeric values. The only way to go would be to replace this rule by n rules, each matching a number/value (or range of numbers) and filling the corresponding cell(s) with a different color

4 Replies

Lorenzo
Silver Contributor
Aug 13, 2022
Hi DBrydon

Not sure this will work because you don't disclose the Conditional Formatting rule that Applies to Q32:Q81 to highligh Subtotals in green

Edit that Conditional Formatting rule. It currently Applies to: $Q$32:$Q$81
Add ,$F$32:$F$81 at the end:
- DBrydon
  Copper Contributor
  Aug 13, 2022
  Lorenzo Sorry for not providing all the necessary info. I tried to apply your suggestion (hopefully I got that correct :-)), however, no changes occurred.
  
  I've tried to capture the Conditional Formatting screen for your review.
  - Lorenzo
    Silver Contributor
    Aug 13, 2022
    DBrydon
    
    - No reason to change the 1st rule (filling grey) as it doesn't apply to your Subtotal col. Keep it is was
    - 2nd rule (filling red) can be adapted to Apply to F32:F81
    - 3rd rule (Graded Color Scale), no way. Such rule apply to a single column only and that column must contain numeric values. The only way to go would be to replace this rule by n rules, each matching a number/value (or range of numbers) and filling the corresponding cell(s) with a different color

Forum Discussion

Highlight cells on the results of calculated & conditionally formatted cell

4 Replies

Resources