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

HLS811

New Member
Joined
Oct 27, 2002
Messages
40
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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...)
 
Upvote 0
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.
 
Upvote 0
Re: IF/THEN formula for conditionally formatting part of a r

Just curious...

Why do you use =UPPER() ?
 
Upvote 0
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. :oops: Stupid, stupid, stupid.

Anyway, that's just to make sure that Upper, upper, and UPPER all make the conditional formatting work.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
Re: IF/THEN formula for conditionally formatting part of a r

You should use the "Formula is:" option.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top