Conditional Formatting

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 2, 2005
Messages
71
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 2, 2005
Messages
71

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

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
Top