Ask a question »

Excel/VBA - Color part of a column

May 2016

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


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.

For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by jak58. - Latest update by Jeff
This document entitled « Excel/VBA - Color part of a column » from CCM ( 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.