Colour part of Column usine Excel VBA (2003)Solved/Closed

Question

Hello, 
I have searched and searched for a way of doing this but cannot find anything - so coming to this forum to ask for assistance.
I have code to colour part of a row based on what is input into a particular column, but i'd like to trnaspose this so that part of a COLUMN is coloured in depending on what is entered in a row. I have tried and tried and can't get it work properly, please help!

Private Sub Worksheet_Change(ByVal Target As Range)
' When a change is made in the worksheet...
    
    If Not Intersect(Target, Range("B3:B100")) Is Nothing Then
    '...to any cells from B3 to B100 (Role)...
        
        If Selection.Cells.Count > 1 Then Exit Sub
        '(exit reoutine if changes made to more than one cell at a time - prevents crashing
        
            Select Case Target
            
                Case "Manager"
                '...check if the cell contains "Manager"...
                Range("A" & Target.Row & ":AG" & Target.Row).Interior.ColorIndex = 36
                '...and if so change the colour of the cells in that row, from B - AH to pale yellow.
                
'other cases in here....
                
            End Select
        End If
End Sub

Configuration: Windows XP / Internet Explorer 8.0

TrowaD · Answer

Hi TC,

Here you go:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A3:L3")) Is Nothing Or _
Selection.Cells.Count > 1 Then Exit Sub

Select Case Target

Case "Manager"
Range(Cells(1, Target.Column), Cells(30, Target.Column)).Interior.ColorIndex = 36

End Select
End Sub
Best regards,
Trowa

Colour part of Column usine Excel VBA (2003)

1 response

Similar discussions

Subscribe To Our Newsletter!