Changing Cell Color based on value (Not same Cell)

[youga924]

Hi! I would like to know if there's a way to make a cell change color depending on the value of another cell, i have minimum knowledge about conditional formatting but I'm guessing it can only be used to condition the cell with the value as as I'm trying to color a different cell than the one containing the value, I suppose I'd need some If statement code thingies or such... HALP PLX?

 

[Marcílio_Lobão]

youga924, Good morning.

Yes, you can use any kind of formula to starts a Conditional Formatting.

Please, give us some more details to easier us help you.

 

[britsgal]

Go into "conditional formatting" select "new rule" then select "use a formula to determine which cells to format. If you want to format cell E1 based on the value in another cell enter =E1=A1 and select how you want E1 to be formatted (yellow fill for example). Then go back to "conditional formatting" and select "manage rules" you should then be able to add additional criteria for E1. Once you enter the 2nd rule you should just be able to keep hitting "new rule" to add as many as you want. Hope this makes sense as I wasn't able to post a screen shot for your reference.

 

[youga924]

I'm doing a kind of checklist which is completed by inputting 0's or 1's, depending on the sum of these numbers is whether it's incomplete or not, for example, sum=0 results in red color, sum between 1-11 results in yellow color fill and 12 (total number of elements) results in green fill... but all this checklist is part of a bigger checklist... all of this is just one of several titles or bullets in the master checklist so to speak... i don't know if i'm being clear :S

what i want to do is... depending on the sum of the checklist.. copy that format to a cell which would be adjacent to the BULLET or TITLE for this specific part of the master checklist as to see which bullet points or tasks are still undone or incomplete... all of these subgroups will be collapsible/expandible.

 

[youga924]

Go into "conditional formatting" select "new rule" then select "use a formula to determine which cells to format. If you want to format cell E1 based on the value in another cell enter =E1=A1 and select how you want E1 to be formatted (yellow fill for example). Then go back to "conditional formatting" and select "manage rules" you should then be able to add additional criteria for E1. Once you enter the 2nd rule you should just be able to keep hitting "new rule" to add as many as you want. Hope this makes sense as I wasn't able to post a screen shot for your reference.

I think this could very much be the way to go, thanks! except i don't know how to format one in terms of another... do all rules begin with "="? Am i just supposed to totally format one of them and then copycat that onto the other cell? so to speak.

 

[marcucciboy2]

Would you mind creating a table in a reply to show an example of what a subgroup looks like?

When you go to type a reply, at the bottom right click the "Go Advanced" button and then you'll be redirected to a reply page that has table tools under the font controls.

Thanks

 

[britsgal]

Yes, the rule is always = then select the cell you want to format then do = and select the sell you want to use for the format requirement.

 

[Krrrcko]

I would actually like to know how to do this as well, but the code for it in VBA rather than using the conditional formatting box.

I have two cells that I would like the color the first cell based on the conditions of both cells.
Example:
If A1= "Phase 1", B1= (>=-20) then color A1 green
else
A1= "Phase 2", B1= (-6 To -19) then color A1 yellow
else
A1= "Phase 3", B1= (<=5) then color A1 red

Plus I have to repeat this 7 times for the different words in A1, and change the code for the numbers. I also wrote a Case statement that defines red, yellow and green in a seperate cell C1. But it doesn't like the negative numbers, and doesn't always give me the right result for yellow.

Function LaunchStatus(G As Range, I As Range) As String
    Dim sLaunch As String
    Select Case G.Value
        Case "Phase 1", "Phase 2", "Phase 3"
            ' Color determined by column I
            Select Case I.Value
                Case Is <= (-20):              sLaunch = "Green"
                Case (-6) To (-19):           sLaunch = "Yellow"
                Case Else:                        sLaunch = "Red"
            End Select

 

[youga924]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]title 1[/TD]
[TD][/TD]
[TD]expand[/TD]
[TD]close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]title 2[/TD]
[TD][/TD]
[TD]expand[/TD]
[TD]close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]title 3[/TD]
[TD][/TD]
[TD]expand[/TD]
[TD]close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Element[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-B[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-C[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-D[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]title n[/TD]
[TD][/TD]
[TD]expland[/TD]
[TD]close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is roughly what it looks like... basically i have elements which can be either satisfactory (1) or unsatisfactory (0). If you look at the table you'll see I pasted emoticons because i have no idea how to color the cells here ' but basically smiley faces would stand for green color or "all satisfactory" and the red icons would be red cells meaning not everything is satisfactory or being met. So basically im trying to put a color status on the title of that expanded section depending on the "TOTAL" cell because that's where i can tell if all values are 1's... i hope its clearer now

THANKS!!