Change color for specific number of cells by value [Solved]

ChrisK - Apr 17, 2018 at 05:49 AM - Latest reply: TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen
- Apr 19, 2018 at 10:47 AM
I have a excel sheet with data of planned and pending projects. In column "I" it states number of weeks required for completion of the project. Each row has separate projects listed. Column "J" to "BH" are blank and each cell in that column for that row indicates a week. So if the value in Column "I" Row "2" is 4, I want excel to color the cells inline with that project row. i.e. color columns J to M in row 2.
Can someone help me. I'm familiar with VBA as well.... So any help is appreciated.
See more 

Your reply

3 replies

TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Updated by TrowaD on 17/04/18 at 11:54 AM
0
Helpful
Hi Chris,

Give the following code a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Range(Cells(Target.Row, 10), Cells(Target.Row, 60)).Interior.ColorIndex = xlNone

If Target.Value = 0 Or Target.Value = vbNullString Then Exit Sub

Range(Cells(Target.Row, 10), Cells(Target.Row, 9 + Target.Value)).Interior.ColorIndex = 3
End Sub


When red isn't your color, then have a look at the following link. The table shows you the color index number for the available colors.
http://access-excel.tips/excel-vba-color-code-list/

Best regards,
Trowa
Thank you soo much... This is super awesome and does exactly what I want...
TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Apr 19, 2018 at 10:47 AM
That's great, thanks for the feedback!
Respond to TrowaD