Color coding cells with drop down list

dsmith2115 

We can try modify the solution to still use Conditional Formatting effectively but keep it compatible with what Excel allows.

Here is a approach:

1. Apply Conditional Formatting Column by Column:
• Column O: Check values in Blad2!C.
• Column P: Check values in Blad2!D.
• And so on, adjusting the formula for each column.
2. For Column O (comparing with column C in Blad2)

Formula for Conditional Formatting in column O:

=COUNTIF(Blad2!$C:$C, $L7) > 0

This formula checks if the value in $L7 exists in Blad2!C.

1. For Column P (comparing with column D in Blad2)

Formula for Conditional Formatting in column P:

=COUNTIF(Blad2!$D:$D, $L7) > 0

This formula checks if the value in $L7 exists in Blad2!D.

1. For Column Q (comparing with column E in Blad2)

Formula for Conditional Formatting in column Q:

=COUNTIF(Blad2!$E:$E, $L7) > 0

This checks if the value in $L7 exists in Blad2!E.

1. Apply these formulas to each column:
• For column R, compare with Blad2!F.
• For column S, compare with Blad2!G.
• And so on, until you cover all the columns you need.

Steps to Apply Conditional Formatting:

1. Select the First Column (e.g., O7):
• Go to Home > Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
2. Enter the Formula: For column O, enter:

=COUNTIF(Blad2!$C:$C, $L7) > 0

1. Set the Format: Choose a color to highlight the cells where the formula returns TRUE.
2. Repeat for Each Column:
• Apply a similar rule to column P with the formula:

=COUNTIF(Blad2!$D:$D, $L7) > 0

• Continue this process for all the columns that need conditional formatting.

Why This Works:

• COUNTIF is fully supported in Conditional Formatting and checks whether the value in column L (e.g., L7, L8, etc.) exists in the corresponding column in Blad2.
• The formula dynamically adjusts for each row (using $L7, $L8, etc.) but stays fixed to compare with the appropriate column in Blad2.

Limitation:

Unfortunately, Excel's Conditional Formatting does not support complex formulas that rely on INDIRECT, INDEX, or dynamic ranges in the way regular formulas do. Therefore, a manual adjustment per column is necessary for this case.

This approach, while a bit repetitive, should meet your needs and be fully compatible with Conditional Formatting. The text, steps and functions were created with the help of AI.

Hope this will help you.