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]

On the second problem, to make A1:A16 red if A17 contains the word "RED" . . .

Select cell A1.
Open the Conditional Formating Dialog Box, New Rule, Use a Formula to determine which cells to format, Format values where this formula is true, =$A$17="RED", apply a red fill format, OK, then in "Applies to" specify the range A1:A16.

 

[Blaedel]

Hi Gerald,

So for the code, I'll try again with spaces: =IF(AND(J5 > TODAY()+1;J5 < TODAY()+60);"Initiate calibration";IF(J5="Not Qualified";"DO NOT USE";IF(ISBLANK(J5);"";IF(J5 < TODAY();"DO NOT USE";IF(J5="N/A";"No calibration";"Calibration OK")))))

For the red colouring we are getting very close now . What I maybe should have mentioned was that I want to apply that rule to every row (one for its own), with out creating 700 rules from A1 through A700. So If H5-H10 says "Green" , H11 says "RED" and H12 through H700 says "YELLOW" I want a rule that only colour the row A11-G11 RED. Is that possible with the conditional formatting dialog box?

Thank you so much for your assistance it very much appreciated

 

[Gerald Higgins]

OK let's try and address these one at a time.

For the red colouring, I know roughly what you mean, but not exactly.
When specifying the formula to use in the CF dialog box, instead of
=$A$17="RED"

try experimenting with
=$A17="RED"
and
=A$17="RED"

And make sure that the format applies to the whole range A1:G700, or whatever your range is.