Excel/VBA - Color part of a column

January 2017


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.

Related


Published by jak58. Latest update on November 6, 2012 at 08:22 AM by Jeff.
This document, titled "Excel/VBA - Color part of a column," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).