IF/THEN formula for conditionally formatting part of a row?

[HLS811]

Hi All.. OK - I'm stumped (its not tough to do..)

I want to add a formula which will make a portion of a row highlighted in a specific color based on the information from one of the cells.. for example -
If in cell A1 the word HIGH is entered, I'd want Cells B1-B4 to be highlighted in Green...
If the same cell has MID entered I'd want that range to be yellow, and if LOW is entered, I'd want it to be in red...

Is that possible...?

Thanks in advance

 

[Tazguy37]

Re: IF/THEN formula for conditionally formatting part of a r

You can use conditional formatting on cells B1:B4 with 3 conditions:
=UPPER(A1)="high"
=UPPER(A1)="mid"
=UPPER(A1)="low"

and choose the format you want for each one. You could do this just for B1, and use the Format Painter for the other cells.

 

[HLS811]

Re: IF/THEN formula for conditionally formatting part of a r

Thanks!! That sort of helps...

I didn't mention, but i'm going to be entering other data in cells B1:B4, if they are left blank then your suggestion seems to work, but once I enter data it goes back to white...

I had to enter the conditional formatting as " if cell value is NOT equal to =UPPER(A1)="high" (mid/low)" - it only seems to be working with the first condition.

Is there another way around this..?

(And I don't know what the Format Painter is that you're referring to...)

 

[Tazguy37]

Re: IF/THEN formula for conditionally formatting part of a r

Oops. Had to change the conditions to use absolute references, like so:
=UPPER($A$1)="high", and so on.

I was able to enter formulas, other text, values, etc. into cells B1:B4, and everything worked just fine.

Also, the Format Painter will allow you to copy a cells formats to other cells quickly. It's in the Format category if you customize the toolbars.

 

[fairwinds]

Re: IF/THEN formula for conditionally formatting part of a r

Just curious...

Why do you use =UPPER() ?

 

[Tazguy37]

Re: IF/THEN formula for conditionally formatting part of a r

You know, that was really stupid of me. It should read "HIGH", etc. for all the conditions. Stupid, stupid, stupid.

Anyway, that's just to make sure that Upper, upper, and UPPER all make the conditional formatting work.

 

[fairwinds]

Re: IF/THEN formula for conditionally formatting part of a r

But you know "=" is not case sensitive.

=EXACT(... would be and thus need UPPER

 

[Tazguy37]

Re: IF/THEN formula for conditionally formatting part of a r

Ah, I gotcha. Seems pretty silly to me that "high" and "HIGH" without the use of exact are equal.

 

[HLS811]

Re: IF/THEN formula for conditionally formatting part of a r

I still can't get it to work... For some reason, if i leave it as 'Equal to' I can't use any text in the fields, if I change it to 'Not Equal to' it works, but only wiht one color (the first condition).

 

[fairwinds]

Re: IF/THEN formula for conditionally formatting part of a r

You should use the "Formula is:" option.