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

Question

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.

TrowaD · Answer

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

Change color for specific number of cells by value

1 response

Subscribe To Our Newsletter!