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 o 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" heck 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.
