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

- - Latest reply: TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
- 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

1 reply

Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
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
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
That's great, thanks for the feedback!
Respond to TrowaD