Thanks, but that's not exactly what I'm looking for (I did a pretty poor job of explaining), so instead of using a hypothetical let me just give you the real situation. I have formatted column D so that if it says--for example--"Proposal," the fill in that cell will turn red, if it says "Post-Proposal" the fill will turn orange, etc (there are 4 more categories). Columns H through Q have data that relates to the respective color in column D, so if any of those cells contain information I want them to be color-coded. However, I do not want blank cells in H:Q to have any filling. So-far, so-good; I can conditionally format H:Q so that if data is entered it automatically color codes. The problem is that if i change column D--say from Proposal to Post-Proposal (ie red to orange)--I want all of the cells in that row of H:Q that contain data to also change color. Is there a way that I can link the cells in column D to the cells in H through Q so that when column D changes color, H:Q will do so as well, all while maintaining the previous condtionallity of filling only non-blank cells?

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

Attached Files

	sample2-conditional formatting.xlsm (19.4 KB, 24 views)
	sample-formatting with macro.xlsm (27.7 KB, 17 views)