Conditional Formatting

Posted by Glenn on March 12, 2001 7:20 AM

OVERALL PROBLEM
I want to fill some cells in Red, some in Blue and some in Green.

WORKSHEET SPECIFICS
I have several columns of numbers, F4:Q14. The cells in Column C, C4:C14 contain the numbers 20,16,17,18,19,18,17,20,19 and 15. Finally, Column D D4:D14 contain either 0,1 or 2.

SPECIFIC PROBLEMS
Since each Column F-Q has a different color scheme criteria I will use Column F as an example.

For each Column C (C4:C14) cell that contains a 16,17,18 or 20 I want to fill the cells (in Red) in Column F that have a 16,17,18 or 20 in Column C.
ALSO
For each Column C (C4:C14) cell that contains a 16,17,18 or 20 AND either a 1 or 2 in Column D I want to fill the cells (in Blue) in Column F that have a 16,17,18 or 20 in Column C AND either a 1 or 2 in Column D.
FINALLY
For each Column C (C4:C14) cell that contains a 15 or 19 AND either a 1 or 2 in Column D I want to fill the cells (in Green) in Column F that have a 15 or 19 in Column C AND either a 1 or 2 in Column D.

Any help would be greatly appreciated.

Thanks in advance,
Glenn

Posted by Mark W. on March 12, 2001 1:59 PM

Glenn, I'll be more than happy to take a look at
your request if you'd do us a favor by providing
a representative sample of your data set. It'll
be easiest for me to get it into my worksheet if
you'll send it as an array constant.

Using an empty cell that's not included in your
data set, type an equal sign in the formula bar,
select your representative data, type Cntrl+=,
and copy and paste the bracketed data (including
the braces) into a follow-up posting.

Posted by Glenn on March 12, 2001 5:18 PM

Mark-

=A3:M14Cntrl+=,

I followed your instructions but this doesn't look correct. I have a small worksheet that details the problem. I can email it to you if you'd like.

Thanks
Glenn

Posted by Mark W. on March 12, 2001 8:45 PM

Glenn, Ctrl+=, means press the control and equal keys. So, go to an empty cell type =A3:M14, then simultaneously press the control and equal keys. This will translate the cell reference, A1:M14, into an array constant. Copy that array constant and paste it into a follow-up posting. Note: it may not be necessary to include all rows and columns in your selection. Keep in mind that all we need is a repesentative sample of your data.

Posted by Glenn on March 13, 2001 6:34 AM

Mark-

Thanks for the clarification: Below is the sample data.

Data:

{"PATRON",0,"Booth","Seat",0,0,1,2,3,12,13,23,123;"Velarde",0,20,1,0,16,12,15,16,13,15,15,12;"Mitchell",0,16,0,0,13,8,13,14,8,13,13,8;"Borne",0,17,2,0,14,14,14,14,15,14,14,13;"Johnson",0,18,0,0,14,10,13,14,10,14,13,11;"Marr",0,19,1,0,10,10,11,10,9,11,11,8;"Peters ",0,18,1,0,14,10,13,14,10,14,13,11;"Charles",0,17,1,0,17,14,17,17,15,17,17,14;"Mann",0,16,0,0,12,10,12,13,10,12,12,10;"Tolbert",0,20,1,0,15,12,14,15,13,14,14,12;"Michaels",0,19,0,0,16,13,17,16,12,17,17,12;"French",0,15,0,0,10,9,10,10,8,10,11,9}

Glenn

Posted by Mark W. on March 13, 2001 7:31 AM

Glenn, after selecting cell F2 choose the Format
Conditional Formatting... menu command. Define
your conditions as follows:

Condition 1 "Formula Is" =AND(OR($C2=16,$C2=17,$C2=18,$C2=20),$D2=0)
and assign a Red pattern

Condition 2 "Formula Is" =AND(OR($C2=16,$C2=17,$C2=18,$C2=20),$D2&LT;&GT;0)
and assign a Blue pattern

Condition 3 "Formula Is" =AND(OR($C2=15,$C2=19),$D2&LT;&GT;0)
and assign a Green pattern

Press OK to close the Conditional Formatting dialog.
Copy cell F2. Select cells F3:F12 and perform
and Paste Special...Formats.

When done Velarde, Borne, Peters, Charles and Tolbert
will have blue patterns applied to their corresponding
values in column F. Mitchell, Johnson and Mann will
have red. And Marr's will be green.

Posted by Glenn on March 13, 2001 1:43 PM

Mark-

BINGO!! Following your instructions I got the conditional formatting to generate the desired results.

Thanks
Glenn