0
Thanks

A few words of thanks would be greatly appreciated.

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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Related

Published by . Latest update on by Jean-François Pillou.

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 (https://ccm.net/).

0 Comments