Unable to edit source list in data validation in Excel

FFAMATO 

It seems like you want to apply conditional formatting to cells in two different ranges (D5:AE370 and AO1:XX1) based on the values in the cells of the range AO1:XX1. Specifically, you want each unique value in the range AO1:XX1 to have a different background color in both ranges.

For this task, you can use a combination of conditional formatting and a helper column.

Here's a general approach:

1. Helper Column:
• In an empty column (e.g., column AB), use the following formula in cell AB5 and drag it down to AB370:

=MATCH(AO1, $AO$1:$XX$1, 0)

1. This formula will return the position of the value in AO1 within the range AO1:XX1.
2. Conditional Formatting:
• Select the range D5:AE370.
• Go to "Conditional Formatting" in the ribbon.
• Choose "New Rule" and select "Use a formula to determine which cells to format."
• Enter the following formula:

=AB5<>0

• Set the format (e.g., background color) for cells meeting this condition.
• Repeat the same process for the range AO1:XX1, using the same formula.

Now, each unique value in the range AO1:XX1 will determine the background color for both ranges. Adjust the cell references in the formula based on your actual data layout.

If you prefer a VBA solution, here's a VBA solution approach for applying conditional formatting based on the values in the range AO1:XX1 to both ranges (D5:AE370 and AO1:XX1). This code assumes that you want each unique value in AO1:XX1 to determine the background color for the corresponding columns in both ranges.

Vba Code is Untested, make a backup before use the code.

Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Dim targetRange1 As Range, targetRange2 As Range
    Dim headerRange As Range
    Dim cell As Range
    Dim colorIndex As Integer
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Change to your actual sheet name
    
    ' Set the target ranges
    Set targetRange1 = ws.Range("D5:AE370")
    Set targetRange2 = ws.Range("AO1:XX1")
    
    ' Set the header range
    Set headerRange = ws.Range("AO1:XX1")
    
    ' Clear existing conditional formatting
    targetRange1.FormatConditions.Delete
    targetRange2.FormatConditions.Delete
    
    ' Loop through each cell in the header range (AO1:XX1)
    For Each cell In headerRange
        ' Find the color index based on the value in the header cell
        colorIndex = WorksheetFunction.Match(cell.Value, headerRange, 0)
        
        ' Apply conditional formatting to the first target range (D5:AE370)
        With targetRange1.FormatConditions.Add(Type:=xlExpression, Formula1:="=COLUMN(" & cell.Address & ")=" & colorIndex)
            .Interior.Color = RGB(255, 0, 0) ' Change the color as needed
        End With
        
        ' Apply conditional formatting to the second target range (AO1:XX1)
        With targetRange2.FormatConditions.Add(Type:=xlExpression, Formula1:="=COLUMN(" & cell.Address & ")=" & colorIndex)
            .Interior.Color = RGB(255, 0, 0) ' Change the color as needed
        End With
    Next cell
End Sub

Make sure to replace "YourSheetName" with the actual name of your sheet. This code uses the MATCH function to find the position of each value in the header range (AO1:XX1) and then applies conditional formatting based on the column index in both target ranges. Adjust the color as needed in the code.

To run this code, press Alt + F11 to open the VBA editor, insert a new module (Insert > Module), and paste the code into the module. You can then run the macro by pressing F5 or using Run > Run Sub/UserForm from the menu. The text and steps were edited with the help of AI.

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.

FFAMATO 

The download doesn't work unfortunately. Perhaps the suggestion in the attached file works for you. I've made an example for the entries in cells AO1:AQ1 which requires 3 different rules for conditional formatting if you want different colors for all jobs. If there are 608 different jobs in range AO1:XX1 and you want a different color for each job it requires 608 rules for conditional formatting.