# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Change cell color using if statement, NOT conditional formatting

## blucky

Hello,

I am currently try to have cells change color according to their value. I was wondering if there was a way to do this by using an "if" statement. I am aware of conditional formatting, but I cannot copy and paste this formula so I would prefer to use an if statement.

For example, I have a value in "A2", and if "A2" is >= "B2" then it is green, if it is >="C2" and <="B2" then it is yellow and if it is <="C2" it is red. 

The values of C2 and B2 change depending on demand. Is there a way to use an if statement to do this?

Thank you!

----------


## Richard Buttrey

Hi,

Not without incorporating a macro to react to the change event and then colour the cells. But surely that's the sledgehammer to crack the proverbial nut. What's wrong with a conditional format? That's exactly what it's meant for. 

I don't know what you mean by being unable to copy and paste the formula. If anything you'd want to copy and paste the (conditional) format.

Regards

----------


## Maistrye

Hi,

This may not be what you're looking for.  That disclaimer aside...

With Conditional Formatting, you don't have to specify actual numbers -- you can specify cells that hold those numbers.

The following example assumes A2's color is determined by B2 & C2, A3's color is determined by B3 & C3, etc.  That being the case, for column A, you can set up the Conditional Formatting as follows:

Condition 1:*Type:* Cell Value Is
*Comparison:* greater than or equal to
*Value:* =B2
*Color:* GreenCondition 2:*Type:* Cell Value Is
*Comparison:* greater than or equal to
*Value:* =C2
*Color:* YellowCondition 3:*Type:* Cell Value Is
*Comparison:* less than
*Value:* =C2
*Color:* RedThe actual conditions can be put in several different ways, this is merely one of them.

The only change you would have to make if all the values in column A depend upon just the cells B2 and C2 is to put =$B$2 and =$C$2 in the above.

S

----------


## blucky

Richard, the way I am doing it, I have A2 being compared to B2 and C2, and I am doing this for about 100 different cell values, so right now I am entering one by one the conditions per cell and it is taking forever. I am looking for a way where I can write the conditional formatting once and then apply it to all formulas. When I try to enter relative references such as $B2, the conditional formatting will not accept it. It says it must be a fixed reference, so when I paste it to the A3, it is comparing A3 to C2, B2 instead of C3, B3.

Thanks Maistrye. I think your response is close to what I am looking for, but I don't know where to enter the conditions. Is that if it is done through a macro or can I do that through the conditional formatting in the worksheet?

The only place I find where I can enter in information is under the formula ("Use formula to determine what cells to format") option. Is this what you are suggesting?

Thank you for your help!

----------


## Maistrye

Hi,

This is done in the conditional formatting for the cell (within the worksheet).  In Excel 2003, it's Format->Conditional Formatting... ALT+O+D.  In Excel 2007, it's in the Home section, so ALT+H+L+R)

You can put this conditional formatting in for A2, then copy and paste the formatting onto the other cells you want it applied to.  

I've included a sample of what I mean for the conditional formatting setup for cell A2 for both Excel 2003 and Excel 2007. 

S

----------


## Maistrye

Oh ... just re-read what you said.  Ignore the Excel 2003 bit and just look at the 2007 example attached to the previous message.

You'd do this for "Format only cells that contain".  Just make sure that you have them in the correct order and check off the "Stop If True".

The "Applies to" section should reference every cell you want these applied to.

S

----------


## blucky

Thank you Maistrye. I think your way would work except each cell has a different upper and lower limit reference, and I think in your example they are all being compared to the same reference point.




```
Please Login or Register  to view this content.
```


The only negative is that the code is not dynamic (I didn't have much time and I am by far not an expert in VBA coding).

Thank you all for your help and suggestions.

----------


## Blhultine

I am in the process of making an appointment calendar in Excel. One problem I am facing is that once I have pulled the length of the appointment from a pull down menu, I want the respective cells to change color depending on length of the appointment. For example in C3 I select 60 I want D3:D6 to change colors to indicate a block of time.

Thanks!

----------


## Richard Buttrey

_Your post does not comply with Rule 2 of our Forum_ RULES. You shouldn't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Regards

----------


## ecronic

Can the conditional formating be used for displaying and hiding text in a particluar cell....

----------


## teylyn

ecronic, 

Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## onin18

Somebody please help! Getting crazy already for how many days...  Already tried so many code in VBA as seen in the sample here in every forum I have seen, but in my excel it always have error... I want to count how many specific color background in A coloumn, B coloumn, C column, D column and E column it start in A4 up to A2004; B4..B2004 and so on until E4..E2004.. But because it has background color because of conditional formatting I dont know to do it, even in kutools cannot count by color because it is not manually highlighted... I want to put the number of cell with highlight in A1, B1, C1, D1, E1 (I already shade colour from A2 to E2 accoridng to the colour I want). Cell A is red, B is purple, C is blue, D is green, E is organge. All the data in a column is all different number no repeat number (all 4 digit number). If anybody can help me can you please write step by step 'coz I didnt study this VBA. Thank you very very much in advance...

----------


## arlu1201

Onin, unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## onin18

> Onin, unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.



How to post thread in my own thread? sorry bit dummy...   :Frown:

----------


## Sgt Rock

To inform someone to start a new thread without specifying how in blue blazes to do so is 
simply in poor taste!! Not everyone is a guru and those that "think" they are should be 
reminded from whence they came - i.e., they too were uneducated once upon a time.

I'd be interested in knowing if this has EVER been answered with simple VBA code and 
NOT CONDITIONAL FORMATTING. Every time someone asks about VBA to change background colors 
the main response is about using CONTIDITIONAL FORMATTING. Perhaps the folks reading the
query do not understand the difference between VBA and CONDITIONAL FORMATTING or they
do not know how to write VBA code?

----------


## jeffreybrown

> I'd be interested in knowing if this has EVER been answered with simple VBA code and NOT CONDITIONAL FORMATTING.



Yes, this has been answered before on many different forums and websites on how to use conditional formatting with VBA.

If you have a question, yes it is a *Forum rule* "on this site" to not post your question inside of another member's post.

*Posting a new thread*Click on *Forum* on the toolbar (top left)Click on *Sub Forum* appropriate to your queryOnce in that *Sub Forum*, at the top left below the toolbar there is a navigation button which says, *+ Post New Thread*From there it should be pretty straight forward, but if not, click on *FAQ* on the toolbar for additional assistance

----------


## FDibbins

> To inform someone to start a new thread without specifying how in blue blazes to do so is 
> simply in poor taste!! Not everyone is a guru and those that "think" they are should be 
> reminded from whence they came - i.e., they too were uneducated once upon a time.



The whole purpose of this forum is for people to ask for help on how to do things (mainly in  excel, but alos other computor-related questions), so if you dont know how to starta new thread, it is a simple matter to ask how to do that.  Most forums follow the same policy of not allowing "hijacking" other member's threads, so this not something specific to us  :Smilie: 


When you join, you are asked to read the rules - I know many do not - but pretty much everything you need to know on participating with us, is covered there
I fully understand and agree that not everyone is a guru, and that not everyone knows how to do these things, but the vast majority of new members that are asked to start their own thread on this, dont seem to have a problem - and few those that do, have asked how to start a new thread  :Cool: 





> I'd be interested in knowing if this has EVER been answered with simple VBA code and NOT CONDITIONAL FORMATTING. Every time someone asks about VBA to change background colors the main response is about using CONTIDITIONAL FORMATTING. Perhaps the folks reading the query do not understand the difference between VBA and CONDITIONAL FORMATTING or they do not know how to write VBA code?



Not understanding the difference is a fairly common situation, and often, regular CF will provide what they want.  If the members really does need a VBA solution, this will - and has - been provided  :Smilie:

----------


## Diogennifer

I still want to know the answer to "change cell color with IF statement, not conditional formatting."  

I've seen a few suggestions where one uses VBA to copy format from a separate sheet, but I would like this question answered as it stands, without VBA and without conditional formatting, just IF statements in each cell. Can it be done?

Conditional formatting is great if you aren't constantly cutting and pasting into a sheet, which my users are.  I can't seem to train them to reset the range over and over again.  But they ARE used to pulling down formulas, and so if the formula in the cell contained the if-then statement about its contents and changed the formatting, I'd have an easier time of it.

----------


## FDibbins

Diogennifer, welcome to the forum  :Smilie: 

1.  



> I still want to know the answer to "change cell color with IF statement, not conditional formatting."



not possible without using VBA/programming.

2.  Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Diogennifer

How can this be? I repeated the original question, which had never been answered in its original form. Didn't think that constituted a "new" question... sorry!

----------


## Richard Buttrey

> How can this be? I repeated the original question, which had never been answered in its original form. Didn't think that constituted a "new" question... sorry!



In this forum the general subject matter will almost have certainly been asked about before. Even with the richness of Excel there are a limited number of subject matters. What isn't the case of course is that all responses necessarily apply in general. To take your argument to a logical conclusion there are a finite number of question subject matters and we'd maybe only need a hundred threads or so ever.

No, the reason we ask for a new thread is precisely because everyone's application of standard techniques is different and a common thread would soon become a right mess and unfathomable with people chipping in with their own unique layouts and requirements.

----------


## Diogennifer

Oh heavens, Richard, my question was, 'why is repeating the original question exactly as worded somehow off-topic', not, 'wah, why can't I post to any thread I want to about anything at any time.'  Should I quit Diogennifer and get a masculine handle?   

I do see that FDibbins, while telling me I couldn't ask the question in part 2, does seem to have answered it in an unfortunately formatted fragment in part 1. My rephrasing of the original question appears to have struck FDibbins as a new thread (or maybe my anecdotal background information at the end).

Let it be known, to all who read this thread, FDibbins says:

Q: Is change cell color with IF statement, not conditional formatting, possible?
A: Yes with VBA, but not in cell formulas.

----------


## FDibbins

Diogennifer I in no way associated your ID with either male or female, so that has bearing perhaps only in your own mind?

We have rules here which all are expected to abide by.  I, and many other - many many times - have made this request to quite a few new members, and all have complied.  In this case, it may seem that your question is "on-topic", but I have seen many that start off that way, only to digress to something different.

This particular rule is to make sure that we respond to YOUR question, in YOUR thread, and that we dont get muddled up with different people asking apparently similar questions in the same thread, and not being able to track/follow who is talking to who  :Smilie:

----------

