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

Report
-
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
-
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.

1 reply

Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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.
https://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
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
That's great, thanks for the feedback!