Excel/VBA - Color part of a column

I have code to color part of a row based on what is entered into a particular column, but i'd like to transpose this code so that part of column is colored based on the data entered in a row. How to tweak the below code:

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 color of the cells in that row, from B - AH to pale yellow.  

'other cases in here....  

End Select  
End If  
End Sub 

Solution

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 

Thanks to TrowaD for this tip.

See also

Knowledge communities.

• Forum
• Excel forum
• Programming forum

This document entitled « Excel/VBA - Color part of a column » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.