1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Change Range Cell Color Based on Another

Discussion in 'Business Applications' started by RHurlburt, May 5, 2004.

Thread Status:
Not open for further replies.
  1. RHurlburt

    RHurlburt Thread Starter

    Oct 31, 2003
    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.
  2. XL Guru

    XL Guru

    Aug 30, 2003
    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.

  3. RHurlburt

    RHurlburt Thread Starter

    Oct 31, 2003
    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.
  4. RHurlburt

    RHurlburt Thread Starter

    Oct 31, 2003
    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.
As Seen On
As Seen On...

Welcome to Tech Support Guy!

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.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/226709