There's no such thing as a stupid question, but they're the easiest to answer.


Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Change Range Cell Color Based on Another


RHurlburt's Avatar
RHurlburt RHurlburt is offline
Member with 130 posts.
Join Date: Oct 2003
Location: Texas
05-May-2004, 08:46 AM #1
Change Range Cell Color Based on Another
What is the best way to change cell colors based on a cell color (Not a number)?

I have a condition where a cell may have green background (conditional formatting), and on the next update, a different cell may have the green background (same condition, but not the same cell). I would like several cells in that column to change to the same color when this happens, they will be different rows, but will be on the same row as the change occurs ... it is dynamic.
Condition 1:

G8 background color is = green

Change adjoining cells to the right and down to the same color
(H8:K8 and G9:K12) The inclusive range would be (G8:K12)
Condition 2 on update:

G8 is back to normal but H8 is now green

Cells in column G are back to normal but adjoing cells to the right and down should now be green.
(I8:K8 and H9:K12) The inclusive range would be H8:K12)
Here is an example of the conditions above.

Condition 1
Row G H I J K
8 20 22 33 32 35
10 24% 15% 22% 21% 25%
11 80 82 90 88 89
12 44% 45% 48% 47% 50%

Condition 2
8 22 33 32 35 38
10 15% 22% 21% 25% 27%
11 82 90 88 89 90
12 45% 48% 47% 50% 52%

The cells I want to highlight will not be of the same value and there are formulae in every cell.

I haven't got the hang of copying the cells from Excel to the post. sorry about the formatting.

Last edited by RHurlburt; 05-May-2004 at 09:15 AM..
XL Guru's Avatar
Senior Member with 2,702 posts.
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
05-May-2004, 10:28 AM #2
In (nearly) simplest terms, and assuming from what you say that there'll NEVER be a time when G8 AND H8 are BOTH green, then

If Range("G8").Interior.ColorIndex = 10 Then
Range("G8:K12").Interior.ColorIndex = 10
ElseIf Range("H8").Interior.ColorIndex = 10 Then
Range("H8:K12").Interior.ColorIndex = 10
End If

You'll have to get back to us on what sheet event might fire this. & let us know if the actual cond formatting (a) blocks the code and/or (b) remains intact.

RHurlburt's Avatar
RHurlburt RHurlburt is offline
Member with 130 posts.
Join Date: Oct 2003
Location: Texas
05-May-2004, 01:17 PM #3
I never want to say never. I have been caught too many times. Once by Dreamboat when I said I couldn't. She proved I could.

1) The first event is that I have linked a cell on this sheet to one particular cell on another sheet. It is this cell that I use to begin my Conditional Formatting. On the "source" sheet, that cell has a value that changes AND table column label changes as well...such as from one quarter to another. At any rate, the cell I am linking to is ALWAYS the first cell in the table that consists of estimates.

2) I want to indicate on the sheet where we are doing the green that this is the FIRST available quarter estimate and reflect that all the next columns are based on the estimate, not actuals.

3) So, my conditional formatting takes the linked cell and uses it to identify the estimate in the new table. What I have done on this sheet is to identify values by quarter, actual and estimated. The tables shift on this page due to some forecasts thru 2005, some thru 2006. I know that in most cases, NO estimates will be for the first quarter of this year (2004) (those will be actually reported), so I set my conditional formatting accordingly.

Since the whole range, row 8 in this case, is formatted the same way, it is possible that another cell could meet the condition and be green as well (it would be an estimated value as well). So, G8 and H8 could be green at the same time, however, if G8 is green, the forumla would color H8 and the subsequent cells anyway. BUT if H8 meets the condition and G8 does not, then only H8 would be colored and the subsequent cells should follow. This is to say that I8, J8 or K8 could meet the condition as well. But, I would ALWAYS want to base the formula on the FIRST cell in that range (G8:K8) that meets the condition.

Not so confusing after all .... or is it?

I had hoped that one could use a formula in Conditional Formating that would allow something like: "IF(Range("G8".Interior.ColorIndex = 10,Range("G8:K12").Interior.ColorIndex = 10,1)". And "condition" the range for each of the cells affected. But, not sure it will do the formula thing. I tried a couple but got "nasty messages". (Or do an "IF(OR( ...)) where I can get all the range involved)"

Is "1" the standard background? Or "2". I can look it up.

I haven't had a chance to check (a) or (b) yet, in the process.
RHurlburt's Avatar
RHurlburt RHurlburt is offline
Member with 130 posts.
Join Date: Oct 2003
Location: Texas
07-May-2004, 08:25 PM #4
I have solved my problem with the next best thing. Have set up 3 conditions in Conditional Formatting:
The first condition sets the cell color based on a value change in another cell. (A1)
The second condition sets all the cells in the column below this "cell condition" with a formula:
And a third condition looks at row 8 in the adjacent row, and based on that value, changes their formats:

The first condition for G8:K8 is:
Cell Value = $A$1

The second condition for all the cells below G8 is:
Formula =G$8=$A$1

There is not third condition for the first set of columns (no adjacent column).

In the second set of columns:
The first condition in H8 is:
Cell Value is equal to: $A$1

The second condition is:
Formula: =H$8=$A$1

And the third condition for the cells beneath H8 is:
Formula: =G$8=$A$1

Wish I could invent a fourth condition.

I get two columns colored this way which will indicate that all the data to the right of these, including these two, are estimates.

This will suffice for now until I get smarter.

Last edited by RHurlburt; 07-May-2004 at 08:31 PM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑