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]

I'm trying to stumble through this myself, I think I have to loop but my attempt does not work, does any one know why?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
  Dim i As Integer, j As Integer, k As Integer
    i = 10
    For j = 2 To 100
 
 If Target.Address = Cells(j, i) Then
 
    For k = 11 To 44
    Set Rng = Cells(j, k)
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
    Next k
    Next j
 End If
 
End Sub

Thanks

 

[evnoort]

Don't know what the code does but now the for loops work

Dim i As Integer, j As Integer, k As Integer
    i = 10
    For j = 2 To 100
 
        If Target.Address = Cells(j, i) Then
 
        For k = 11 To 44
            Set Rng = Cells(j, k)
            Select Case UCase(Target.Value)
            Case "1":
                Rng.Interior.ColorIndex = 15
            Case "7":
                Rng.Interior.ColorIndex = 15
            End Select
        Next
        End If
    Next

 

[Bungraman]

Thanks,

It is to loop the "select case" to look first at the value in cell B10 then shade the cells B11 to B44, then loop to look at the adjoining column cell C10 then shade cells C11 to C44, then loop to look at the next column cell D10 then shade the cell D11 to D44 and so forth up to column "CZ"....

But the code doesn't work, but the loop may not sure cause it not doing anything???

Am I coding the right way?

Regards
Bungra

 

[evnoort]

Hi Bungra,

I do not understand you completely.

You want to set the "shading" of the cells B11 to B44 if the cell B10 has an value of 1 or 7? till column CZ?

Regards, Erik

 

[Bungraman]

Hi Erik,

Essentially the code repeats itself until column CZ.

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
 
 If Target.Address = "$C$10" Then
  Set Rng = Range("C11:C44")
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If
 
 If Target.Address = "$D$10" Then
  Set Rng = Range("D11:D44")
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If
 
 If Target.Address = "$E$10" Then
  Set Rng = Range("E11:E44")
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If

[COLOR="Magenta"]blah blah blah  [/COLOR]
End Sub

But I don't want to type this part of the code over 100 times.

 If Target.Address = "$E$10" Then
  Set Rng = Range("E11:E44")
    Select Case UCase(Target.Value)
        Case "1":
            Rng.Interior.ColorIndex = 15
        Case "7":
            Rng.Interior.ColorIndex = 15
    End Select
 End If

I am putting together a 3 monthly rostering tool, so I want to highlight the columns that signify the weekends, days numbered 1 & 7 found on row 10.

But I need to keep a sheet as the master template, so the year can change, the year is at cell A1, hence I need the code to run when cell A1 changes.


Regards
Bungra

 

[evnoort]

This does part of the job, marks row 10 to 44

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, 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

 

[evnoort]

This does the job

Dim row As Integer, col As Integer, i As Integer
Dim Rng As Range

col = Target.Column
row = Target.row

 If row = 10 Then
  For i = row To 44
    If Cells(i, col).Value = 1 Or Cells(i, col).Value = 7 Then
        Cells(i, col).Interior.ColorIndex = 15
    End If
  Next
  
 End If

 

[Bungraman]

Thanks Erik,

The last code you posted, only shaded row10. Row10 does not need to be shaded but I modified it to work as:

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([COLOR="Blue"]row + 1[/COLOR], 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

Do you know how to get it to run when cell A1 changes value?

Regards, Bungra

 

[evnoort]

Check if this works, first check if:

If row = 1 and col =1 then

'previous code

end if