Change cell color using if statement, NOT conditional formatting

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!

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: Green

Condition 2:

Type: Cell Value Is
Comparison: greater than or equal to
Value: =C2
Color: Yellow

Condition 3:

Type: Cell Value Is
Comparison: less than
Value: =C2
Color: Red

The 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

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!

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

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

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.

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!

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

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...

Originally Posted by 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.

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

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?

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.

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!