Avanderh, you have two ways to do this:
with conditional formatting: in this case, you need two rules for every category, because you have 2 ranges : first is column D and second from H to Q
a rule vill apply to this range:
=$D$12:$D$100, and the formula to format is:
=$D12="Post-Proposal"; this rule will take care of column D
the second rule for this category will apply to range =$H$12:$Q$100, and the formula to format is: =AND($D12="Post-Proposal";H12>0)
for other categories, just enter new rule and apply to same ranges, you will only change "Post-Proposal" to "Whatever"
the second way, is to use a macro:
Sub ColorIndex()
Dim planWks As Worksheet
Dim nextRow As Long
Set planWks = Worksheets("Foaie1")
Dim i As Integer
Dim j As Integer
Dim r1, r2 As Range
Application.ScreenUpdating = False
For i = 12 To 300
Set r1 = planWks.Cells(i, "D")
For j = 8 To 17
If r1.Value = "Proposal" Then
r1.Interior.ColorIndex = 39
planWks.Cells(i, j).Interior.ColorIndex = 39
End If
If planWks.Cells(i, j).Value = 0 Then
planWks.Cells(i, j).Interior.ColorIndex = xlNone
End If
Next j
Next i
For i = 12 To 300
Set r1 = planWks.Cells(i, "D")
For j = 8 To 17
If r1.Value = "Post-Proposal" Then
r1.Interior.ColorIndex = 25
planWks.Cells(i, j).Interior.ColorIndex = 25
End If
If planWks.Cells(i, j).Value = 0 Then
planWks.Cells(i, j).Interior.ColorIndex = xlNone
End If
Next j
Next i
For i = 12 To 300
Set r1 = planWks.Cells(i, "D")
For j = 8 To 17
If r1.Value = "other" Then
r1.Interior.ColorIndex = 27
planWks.Cells(i, j).Interior.ColorIndex = 27
End If
If planWks.Cells(i, j).Value = 0 Then
planWks.Cells(i, j).Interior.ColorIndex = xlNone
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
You will find attached samples for both methods, but in my opinion, using conditional formatting is your best choice
|