Color coding cells with drop down list

dsmith2115 

You can achieve dynamic color coding based on the value in column L using Conditional Formatting in Excel without needing to create multiple formulas for each row. Instead, you'll use a formula that will apply to the entire range O:BV and automatically adjust for each row based on the corresponding value in column L.

Here’s how to set up conditional formatting for multiple rows, where the formula adjusts based on the value in column L:

Steps for Color Coding Cells Based on Drop-Down List in Column L:

1. Prepare Your Drop-Down List in Column L:

• Ensure that column L contains the drop-down list, which users will select from. The values in column L will control the formatting in columns O:BV.

2. Set Up the Conditional Formatting:

1. Select the Range:
• Select the range you want to apply the color coding to. In your case, select the range O:BV. Start from the first row of data (e.g., if your data starts in row 7, select O7:BV100 or however far your data goes).
2. Open Conditional Formatting:
• Go to the Home tab on the Excel ribbon.
• Click on Conditional Formatting > New Rule.
3. Use a Formula for Conditional Formatting:
• In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
4. Enter the Formula:
• Use a formula that refers to the corresponding value in column L. Here’s the formula to use:

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

• This formula checks if the value in column L (e.g., L7, L8, L9, etc.) exists in the list in Blad2!$B:$B. The dollar sign before L locks the column, but allows the row number to change as the formatting is applied to different rows.

    5. Set the Format:

• After entering the formula, click Format to choose the color you want to apply to the range O:BV if the condition is met.
• Choose your preferred fill color and click OK.

    6. Apply the Rule:

• Click OK to close the formatting dialog.
• The formatting will now be applied dynamically, based on the corresponding value in column L for each row.

3. Result:

• If the value in column L (e.g., L7) is found in Blad2!$B:$B, the corresponding cells in columns O:BV for that row will be color-coded according to the formatting you set.
• This will automatically apply to all rows in the selected range, meaning you don't need to write individual formulas for each row or column.

Explanation of the Formula:

• COUNTIF(Blad2!$B:$B, $L7) > 0:
• This checks if the value in column L of the current row (e.g., L7) exists in the column B of Blad2. If it exists, the formula returns TRUE, triggering the conditional formatting.
• $L7 locks the column L but allows the row number to change dynamically for each row (e.g., L7, L8, L9, etc.).
• The condition checks if the count of the value in column B of Blad2 is greater than 0, indicating that the value exists.

Alternative Based on Multiple Criteria:

If you have more conditions based on different values in the drop-down list (e.g., color code differently for different values), you can create multiple rules with different formulas for different colors.

For example:

• For one condition, use COUNTIF(Blad2!$B:$B, $L7) > 0 to apply one color.
• For another condition, you might use =COUNTIF(Blad2!$C:$C, $L7) > 0 to apply a different color if the value matches a different list in Blad2.

The text, steps and functions were created with the help of AI.

The example file is from my archives and contains various examples with conditional formatting. Unfortunately I no longer know the source or which website I got it from,

maybe it will help additionally.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.