Assigning color depending on value in Excel 2010

[Blaedel]

Dear All,

I think I need to write my very first VBA macro, but I have no idea on how to begin (other than how to open the VBA editor). Maybe I don't need the VBA but I don't know how to do it with the basic Excel functions. PLease note that I am working in excel 2010.

So what do I want:

I need to make a column that assing a specifik color depending on the wording of a specifik cell. I think in pseudo code it would look a bit like this

If text in K7 = "Control OK" assign a green color to the cell
If text in K7 = "Initiate calibration" assign a red color
if text in K7 = "N/A" assign a grey color
If cell is blank leave a blank cell

That's basically it. How can I do that? Can you help me?

All the best
Martin

 

[Gerald Higgins]

Hi, welcome to the board.

You can do this without VBA, using your basic Conditional Formating.

 

[Blaedel]

Yes I certainly can - I just found out. Thank you for directing me. It worked out perfectly just using the conditional formatting.

While on it, I have two new challenges.

1) Lets say I have a range of if sentences; It looks a bit like this: IF(AND(gd=6;IF(XXX;(IFXXX;"true";"false")) etc. And I want Excel to return "DO NOT COMPLY" If none of the IF's apply? If what is in the cell in question is not X, nor Y nor C etc. Is there a function that does that?

2) I want to colour cells in row A1:A16 red if it say "RED" in A17. Is that possible?

BR
Martin

Hi, welcome to the board.

You can do this without VBA, using your basic Conditional Formating.

 

[Gerald Higgins]

1) Lets say I have a range of if sentences; It looks a bit like this: IF(AND(gd=6;IF(XXX;(IFXXX;"true";"false")) etc. And I want Excel to return "DO NOT COMPLY" If none of the IF's apply? If what is in the cell in question is not X, nor Y nor C etc. Is there a function that does that?

It's difficult to be precise about this because the IF statement you have provided does not look workable, but in principle, you can get your IF statement to do that.
For example, =IF(A1=6,"COMPLIES","DO NOT COMPLY"
For example, =IF(A1=6,"SOMETHING",IF(B2=10,"SOMETHING ELSE","DO NOT COMPLY"))

2) I want to colour cells in row A1:A16 red if it say "RED" in A17. Is that possible?

Yes, this is your basic Conditional Formating.

 

[Blaedel]

Hi again,

Hmm - the IF really works perfectly - perhaps its like the bumble bee? It looks like this in it entirety. Should you feel the urge, please do not hesitate to correct or improve it

[=IF(AND(J73>TODAY()+1;J73<TODAY()+60);"Initiate calibration";IF(J73="Not Qualified";"DO NOT USE";IF(ISBLANK(J73);"";IF(J73<TODAY();"DO NOT USE";IF(J73="N/A";"No calibration";"Calibration OK")))))/CODE]

What I want is if someobe punches something which is not a date, N/A, "Not Qualified" etc. (or misspells) the  

About the colouring - could you assist me a bit further? I can't seem to find the tool that will do what I want, which is (again): if the cell in question with the above outlined code for example returns "Initiate Calibration" all cell in the same row from eg. H1 to H6 turns yellow.

Does that make sense, or...?

Best 
Martin

 

[Blaedel]

Hi again,

Hmm - the IF sentence really works ... reasonably well - Perhaps its somewhat like the bumble bee? I
t looks like the following in it entirety. Should you feel the urge, please do not hesitate to correct or improve it

<today()+60);"initiate CODE]

=IF(AND(J85>TODAY()+1;J85<TODAY()+60);"Initiate calibration";IF(J85="Not Qualified";"DO NOT USE";IF(ISBLANK(J85);"";IF(J85<TODAY();"DO NOT USE";IF(J85="N/A";"No calibration";"Calibration OK")))))

What I want is, if someone types into the J85-cell, something which is not a date, N/A, "Not Qualified" etc. (or misspells). Then the function will return a statement that the word/number etc. is not valid. As the code is now, it works well as long as you type in the exact spelling as is given in the code line (of cause). But unfortunately it returns Calibration OK if I type in a random word or number. That is not the intention.

About the colouring - could you assist me a bit further? I can't seem to find the tool that will do what I want, which is (again): if the cell in question with the above outlined code for example returns "Initiate Calibration" all cell in the same row from eg. A85 to K85 turns yellow.

Does that make sense, or...?

Best
Martin</today()+60);"initiate>

 

[Blaedel]

Hi again,

Hmm - the IF sentence really works ... reasonably well - Perhaps its somewhat like the bumble bee? I
t looks like the following in it entirety. Should you feel the urge, please do not hesitate to correct or improve it

<today()+60);"initiate CODE]
=IF(AND(J85>TODAY()+1;J85<TODAY()+60);"Initiate calibration";IF(J85="Not Qualified";"DO NOT USE";IF(ISBLANK(J85);"";IF(J85<TODAY();"DO NOT USE";IF(J85="N/A";"No calibration";"Calibration OK")))))
What I want is, if someone types into the J85-cell, something which is not a date, N/A, "Not Qualified" etc. (or misspells). Then the function will return a statement that the word/number etc. is not valid. As the code is now, it works well as long as you type in the exact spelling as is given in the code line (of cause). But unfortunately it returns Calibration OK if I type in a random word or number. That is not the intention.

About the colouring - could you assist me a bit further? I can't seem to find the tool that will do what I want, which is (again): if the cell in question with the above outlined code for example returns "Initiate Calibration" all cell in the same row from eg. A85 to K85 turns yellow.

Does that make sense, or...?

Best
Martin
</today()+60);"initiate>

 

[Blaedel]

Hi again,

Hmm - the IF sentence really works ... reasonably well - Perhaps its somewhat like the bumble bee? It looks like the following in it entirety. Should you feel the urge, please do not hesitate to correct or improve it

<today()+60);"initiate CODE]
<today()+60);"initiate calibration?;?Calibration="" ;?No="" NOT="" ;?DO="" USE?;IF(J85="N/A" USE?;IF(ISBLANK(J85);??;IF(J85<TODAY();?DO="" calibration?;IF(J85="Not Qualified" OK?)))))
Here we go: =IF(AND(J85>TODAY()+1;J85<TODAY()+60);"Initiate calibration";IF(J85="Not Qualified";"DO NOT USE";IF(ISBLANK(J85);"";IF(J85<TODAY();"DO NOT USE";IF(J85="N/A";"No calibration";"Calibration OK")))))

What I want is, if someone types into the J85-cell, something which is not a date, N/A, "Not Qualified" etc. (or misspells). Then the function will return a statement that the word/number etc. is not valid. As the code is now, it works well as long as you type in the exact spelling as is given in the code line (of cause). But unfortunately it returns Calibration OK if I type in a random word or number. That is not the intention.

About the colouring - could you assist me a bit further? I can't seem to find the tool that will do what I want, which is (again): if the cell in question with the above outlined code for example returns "Initiate Calibration" all cell in the same row from eg. A85 to K85 turns yellow.

Does that make sense, or...?

Best
Martin</today()+60);"initiate></today()+60);"initiate>

 

[Blaedel]

Why Cant I type In the code line?????

Trying again "=IF(AND(J85>TODAY()+1;J85<TODAY()+60);"Initiate calibration";IF(J85="Not Qualified";"DO NOT USE";IF(ISBLANK(J85);"";IF(J85<TODAY();"DO NOT USE";IF(J85="N/A";"No calibration";"Calibration OK")))))"

 

[Gerald Higgins]

Are you having problems with < and > symbols ?

If so, leave a space after them. It's a known bug/feature on this board.