Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-25-2011, 12:21 PM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default Conditional color fill based on presence of data

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
Reply With Quote
  #2  
Old 07-25-2011, 10:48 PM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsx sample.xlsx (9.3 KB, 26 views)
Reply With Quote
  #3  
Old 07-26-2011, 07:59 AM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 07-27-2011, 04:45 AM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm sample2-conditional formatting.xlsm (19.4 KB, 22 views)
File Type: xlsm sample-formatting with macro.xlsm (27.7 KB, 15 views)
Reply With Quote
  #5  
Old 07-27-2011, 07:27 AM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default

Wow, that worked perfectly. Thanks so much for your help.
Reply With Quote
  #6  
Old 07-27-2011, 07:31 AM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

you're welcome
Reply With Quote
  #7  
Old 07-27-2011, 01:35 PM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 07-27-2011, 07:51 PM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 07-28-2011, 06:39 AM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 07-28-2011, 07:12 AM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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)
Attached Files
File Type: xlsm Copie a sample2-conditional formatting.xlsm (19.7 KB, 16 views)
Reply With Quote
  #11  
Old 07-28-2011, 08:44 AM
avanderh avanderh is offline Conditional color fill based on presence of data Windows 7 32bit Conditional color fill based on presence of data Office 2007
Novice
Conditional color fill based on presence of data
 
Join Date: Jul 2011
Posts: 6
avanderh is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 07-28-2011, 09:10 AM
Catalin.B Catalin.B is offline Conditional color fill based on presence of data Windows Vista Conditional color fill based on presence of data Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I fill the below emty cells with above cell data? Learner7 Excel 8 06-28-2011 12:10 PM
Conditional color fill based on presence of data Conditional data validation (list drop-down) click4akshay Excel 2 04-28-2011 01:51 PM
How can I fill cell color starting from Cell D5 using Conditional formatting instead Learner7 Excel 0 07-08-2010 05:50 AM
Conditional color fill based on presence of data Conditional Expression that Moves Data (challenging) themangoagent Excel 1 02-20-2010 11:29 AM
Conditional color fill based on presence of data Change Automatic Fill Color Leanne PowerPoint 1 11-04-2009 08:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft