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

#### HLS811

##### New Member
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...?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

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

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.

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

Just curious...

Why do you use =UPPER() ?

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.

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

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.

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

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

You should use the "Formula is:" option.

Replies
3
Views
317
Replies
4
Views
158
Replies
1
Views
561
Replies
7
Views
202
Replies
1
Views
403

1,196,497
Messages
6,015,550
Members
441,900
Latest member
Inaschemitex2023

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

### Which adblocker are you using?

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

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