Range Shading based on another cell value

[Bungraman]

Hi,

I have this piece of code that checks the value in B10 then formats (shades) a range of cells B11 to B44.

How do I do it for other columns. For example:

Shade B11 to B44 based on value in B10, Shade C11 to C44 based on value in C10 ..... Shade CZ11 to CZ44 based on value in CZ10.

Also, how do I get the code to run only when the cell A1 changes.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$10" Then
  Set Rng = Range("B11:B44")
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If
End Sub

Can anyone help??

Thanks
Bungra

 

[Bungraman]

Erik.

Because the values are already on row10, the code is not running when Cell A1 is changed. The code will only work if each cell on row 10 is changed manually. I need the code to check the contents already on row10, and apply the shading.

Regards, Bungra

 

[Bungraman]

Please Please Help

This code is very manual. I need it to be a automative process. This code will only fire if the cell value in "row10" is manually changed. Row 10 contains formulas, so when the resultant formula changes it needs to fire, but it doesn't. When I force the code to run it only runs through one column only, I need it to run through columns "B" to "CZ".

Can anyone please help...

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim row As Integer, col As Integer
Dim Rng As Range
 
col = Target.Column
row = Target.row
 
 If row = 10 Then
  Set Rng = Range(Cells(row + 1, col), Cells(44, col))
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If
 
End Sub

 

[Bungraman]

After a couple of gruelling hours, I have managed to get it to work, fluked it!

This is the end code result:

Dim c As Integer, day As Integer
Dim Rng As Range
 
    For c = 2 To 100 'c refers to column number
        day = ActiveSheet.Cells(10, c) 'day refers to row 10
      Set Rng = Range(Cells(11, c), Cells(44, c))
        Select Case day
            Case "1":
                Rng.Interior.ColorIndex = 15
            Case "7":
                Rng.Interior.ColorIndex = 15
            Case Else
                Rng.Interior.ColorIndex = xlNone
        End Select
    Next