Conditional color fill based on presence of data

[avanderh]

Hi,



I am trying to format an excel sheet so that when a specific cell changes color, so does another group of cells. However, I do not want all cells in the group to change color, only the ones that have data entered. For example, if cell A1 is the independent cell, changing its color will change the color of all cells A2:A10, but only for the ones that have something entered in, leaving the rest as no-fill. Furthermore, I would like it so that if I enter data in any of the cells in the group (A2:A10) that it will automatically change color to be the same as cell A1.

To summarize, I pretty much want the group to act as though it is conditionally formatted for non-blank cells, while responding to the color of one particualr cell. Is this possible and, if so, how can I do it?

Thanks,
Alex

[Catalin.B]

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.

[avanderh]

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?

[Catalin.B]

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

[avanderh]

Wow, that worked perfectly. Thanks so much for your help.

[Catalin.B]

you're welcome

[avanderh]

I actually have another question. If I also wanted A4 through C4 to turn grey whenever H4:Q4 < 0, (or, alternatively to turn as no-fill whenever H4:Q4>0) how could I do that using conditional formatting?

[Catalin.B]

Just add new rule, select the range A4:C100, open conditional formatting, add new rule, select to use formula, and the formula to format is:
=sum(H4:Q4)<0, and the color to format is obviously, grey

[avanderh]

I couldn't get that formula to work so that they would all stay grey if H4:Q4<0, so I turned it around and made it so they would be no-fill if H4:Q4>0. This worked for the most part (I just auto-filled all the cells grey so that they would change to no-fill when data was entered), except for columns H, I and J. When data is entered into column H, only column A changes; when data is entered into column I, only column B changes; for column J, only C changes. Any suggestions on how to make the first formula work or fix the problems with columns H, I and J?

[Catalin.B]

in the sample atached, A to C column will turm grey if =SUM($H12:$Q12)<0, this means that these columns will not turn grey if you have, say, 25 in H12, and -20 in Q12, because the sum is positive. If you want A to C to turn grey at any negative value in H12: Q12, you have to say that clearly, dont let me guess. The formula for this case is: =OR(H12<0;I12<0;J12<0;K12<0;L12<0;M12<0;N12<0;O12< 0;P12<0;Q12<0)

[avanderh]

I'm sorry, I was not very clear. I have no interest in negative numbers; when I said I wanted A4:C4 to turn grey when H4:Q4<0, I actually meant that I wanted A4:C4 to be grey only if there was no data entered in H4:Q4 (so greater than or equal to, i guess). So pretty much whenever I put a number into the range of H4:Q100, I want the columns A, B and C from the respective row to turn from grey to no-fill

[Catalin.B]

use this formula with the grey format
=AND(ISBLANK(H12)=TRUE;ISBLANK(I12)=TRUE;ISBLANK(J 12)=TRUE;ISBLANK(K12)=TRUE;ISBLANK(L12)=TRUE;ISBLA NK(M12)=TRUE;ISBLANK(N12)=TRUE;ISBLANK(O12)=TRUE;I SBLANK(P12)=TRUE;ISBLANK(Q12)=TRUE)