# Conditional Formatting

#### pkohli

##### Board Regular
Hi,

I need to apply conditional formatting based on multiple conditions. for eg, If column Q is "H/W - OS EOSL" or "H/W EOSL - OS EOSL TBD" or "H/W EOSL - OS EOSL after 1yr" etc (8 such conditions) then color the background as red. Similarly, 3 conditions for yellow & 2 for green.

I tried using the OR formula but it doesn't seems to be working.

Can someone please advise how to go about it? The data in column q appears as a result of if formulas.

Thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Richard Schollar

##### MrExcel MVP
Hi

You should be able to use an OR as long as you write it out like:

=OR(Q1="H/W - OS EOSL",Q1="H/W EOSL - OS EOSL TBD",...,Q1="H/W EOSL - OS EOSL after 1yr")

and not as:

=OR(Q1={"H/W - OS EOSL",...})

as you can't use array constants in CF.

#### pkohli

##### Board Regular
Hi,

Thanks that worked!

I have written an if statement as follows:

IF(AND(\$O2=\$CA\$1,\$M2=\$BZ\$1),"H/W - OS EOL","")&IF(AND(\$O2=\$CA\$1,\$M2=\$BZ\$2),"H/W EOL - OS EOL TBD","")&IF(AND(\$O2=\$CA\$1,\$M2=\$BZ\$3),"H/W EOL - OS EOL Not Occ","")&IF(AND(\$O2=\$CA\$2,\$M2=\$BZ\$1),"H/W EOL Not Occ - OS EOL","")&IF(AND(\$O2=\$CA\$2,\$M2=\$BZ\$2),"H/W EOL Not Occ - OS EOL TBD","")&IF(AND(\$O2=\$CA\$2,\$M2=\$BZ\$3),"H/W - OS EOL Not Occ","")&IF(AND(\$O2=\$CA\$3,\$M2=\$BZ\$1),"H/W EOL TBD -OS EOL","")&IF(AND(\$O2=\$CA\$3,\$M2=\$BZ\$2),"H/W EOL TBD - OS EOL Not Occ","")&IF(AND(\$O2=\$CA\$3,\$M2=\$BZ\$3),"H/W -OS EOL TBD","")

Is it possible to do the above in some other way as it is long formula and difficult to make changes in it?

#### Richard Schollar

##### MrExcel MVP
I would probably set up a grid with all your possible combinations like the following:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>456</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>789</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>aaa</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1b</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>bbb</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2b</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ccc</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3b</TD></TR></TBODY></TABLE>

Assume it is in A1:D4

Then assume your lookup values (eg from you formula above this would involve O2 and M2) are in A7 and B7 then you need to look these up against the values in A2:A4 and B1:D1 respectively:

=INDEX(\$B\$2:\$D\$4,MATCH(A7,\$A\$2:\$A\$4,0),MATCH(B7,\$B\$1:\$D\$1,0))

which will return the value at the intersection of the matched value in A2:A4 and B1:D1.

Make sense?

#### pkohli

##### Board Regular

Hi

I have now added a new sheet and have listed all the conditions that need to have a background as red by defining a name for this range as red. Similarly for yellow background and green.

How can I use this range in conditional formatting? i.e If A1=red, background as red, A1=yellow, background yellow else green.

#### Richard Schollar

##### MrExcel MVP
Assuming you have a single column list for those values that must be red, name this list (via Insert>Name>Define) let's say you call it RedList, then in the cells that need to be conditionally formatted, apply CF as usual, change to Formula Is and use:

=MATCH(A1,RedList,0)

(this assumes that A1 is the currently active cell). Format as required.

#### pkohli

##### Board Regular
Thank you so much!!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,199
Messages
5,835,933
Members
430,396
Latest member
dzifna

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