Formula or macro to change cell color

ohhaykfrankk

New member
Joined
Jan 29, 2017
Messages
33
Reaction score
0
Points
0
Excel Version(s)
2010
Hey everyone!
Is there a way to change the cell color by using a formula or a macro?

If not, maybe a formula that I can make a conditional format out of like bold or something. It just doesn't work because all numbers over 0 can be either green or red and it's just a lot of work to manually color them.

If so, this is what I'm looking for:

Cells with numbers (most cells are blank) in Columns I, K, M need coloring.
So search H8 (e.g) and find it in C2:C49 (for e.g. we'll say C27 has the text in H8)
When found, if the number in G(27) is 0, color I8(e.g.) green. If the number of G(27) is <0 (or =/0 whichever) then color I8 red.

If it matters, there's also numbers in IKM that are 0 that can stay blank colored because they don't exist in column C.

I hope this makes sense.
 
Conditional formatting can take formulas as a condition, so there isn't really a need to create a UDF to change the colour of cells based on the result of a calculation.

Based on the given info in your sample above, you can try this:

Create a new conditional format in cell I8, by using a formula to determine the formatting.
For the formula, put in: =VLOOKUP($H8,$C$2:$G$31,5,0)<0
Assign RED as the formatting
Accept the rule

Create a second copy of the rule but use this as the formula: =VLOOKUP($H8,$C$2:$G$31,5,0)=0
Assign GREEN as the format
Accept the rule

As the value in G27 changes (assuming it matches H8), the red or green with be applied.
 
Hi and welcome
could you perhaps post a sample file with some data and expected results? ( Click go advanced - manage attachments)
 
As the value in G27 changes (assuming it matches H8), the red or green with be applied.

Thank you!! It didn't work though..The colors don't change

I tried to post a sample piece but there's formulas that go to different pages and each other and it's a big mess. So I'd have to give you the whole thing if you want it.

Basically what I have is a list of things in column C. Column D collects data from different pages AND columns IKM to list how many things I need total. So C8 is cake, D8 says I need 1, H8 is 'strawberry' and I8 is a formula for multiplying D8 by 2. So I need 2 strawberries total. Currently, Strawberry is also C34 so D34 responds that I in fact need 2. But E34 says I already have 2, therefore my total in G34 (which is D-E-F) is 0 so the strawberry quantity I8 should be green.
 
It might be best to upload a (whole) sample as Pecoflyer suggested.

I often try to recreate a scenario to provide the best help I can. This seems to be more complex than your opening post suggests.
 
Back
Top