you can simply do that with conditional formatting:


select range A1:A10, open conditional formatting, new rule, use formula, and use this formula: =AND($A$1>0;A1>0), and format with the colour you want to apply to this range. But if you want to change the colour for this range based on cell values, you can do this only with a simple macro. Just tell us if you need more... Good luck!
You can take a look at the sample atached.

Attached Files

sample.xlsx (9.3 KB, 28 views)

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)