Multiple condition, multiple range formating

In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells.

So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.Please see attached file as an example of final output.

Any help would be greatly appreciated.

Thank you in advance,
Jay

Hello sa02000,

No one has answered your post, because it lacks any concrete examples of what you want to do. You need to detail what your conditions will be and which color you want associated with which condition.

This uses a Worksheet_Change macro to watch row 6 from D6 on. It uses an array of "steps" listed in a Select Case Value test.

All you need to do is enter the step #s in each Condition. After your original 3 samples, I added 3 more sets of random step selections to show the colors in action.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Format a range of cells based on drop selection
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C6:IV6")) Is Nothing Then
Dim i As Integer, v As Integer, c As Variant

i = Target.Column
v = 14  'add row variance
Range(Cells(15, i), Cells(45, i)).Interior.ColorIndex = xlNone

    Select Case Target.Value
        Case "Condition A"   'Orange
            For Each c In Array(1, 2, 7, 11, 15)   'Enter Step #s
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 40
            Next c
        Case "Condition B"   'Lite Green
            For Each c In Array(2, 5, 9, 11, 16, 21, 25, 28, 30)
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 35
            Next c
        Case "Condition C"   'Lite Blue
            For Each c In Array(1, 4, 9, 11, 16, 17, 18, 19, 22, 27, 30)
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 37
            Next c
        Case "Condition D"   'Lavender
            For Each c In Array(3, 8, 9, 10, 12, 13, 19, 20, 24, 29)
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 39
            Next c
        Case "Condition E"   'Pink
            For Each c In Array(6, 7, 8, 13, 14, 17, 19, 20, 23, 28, 31)
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 38
            Next c
        Case "Condition F"   'Grey
            For Each c In Array(1, 10, 20, 30, 31)
                Range(Cells(c + v, i), Cells(c + v, i)).Interior.ColorIndex = 15
            Next c
        Case Else
    End Select
End If
End Sub