Kioskea
Recherche
Ask a question »

Excel/VBA - Color part of a column

March 2015


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.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-vba-color-part-of-a-column.pdf

See also

In the same category

Published by jak58. - Latest update by Jeff
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.