conditional formatting

[mwvirk]

i want to change the cell B1 in red colour by checking the range A1:A300 if its having same value like B1 and if range C1:C300 is 'F' and range D1:D300 is 'D' but if C1:C300 is 'P' then cell B1 should be in yellow colour

e.g. if B1 is ABC then it should look A1:A300 for ABC - when its true then look for C1:C300 for 'F' and D1:D300 'D' - if all true then ABC in B1 should be RED (background colour) and if C1:C300 is 'P' then B1 should be yellow background.

thanks

 

[venkat1926]

select B1

click format-conditional foramtting

under condition 1
"the formula is "
on right window copy this formula

=AND(COUNTIF($C$1:$C$300,"F")>0,COUNTIF($D$1:$D$300,"D"))
and clcik "format" in the middle
choose "pattern" third item at the top
choose red color
click ok
in the window click "add at the bottom
again condition2
"the formula is"
on right window copy
=AND(COUNTIF($C$1:$C$300,"P"),COUNTIF($D$1:$D$300,"D"))

click ok
again in main window click ok

you can copy the formats down copy-pastespecial format if necessary

 

[mwvirk]

thanks venkat1926... i'll try and info u...
(file is in my office where i want to use this formula)

 

[venkat1926]

one small correction to my suggestions.

after that second formula
on right window copy
=AND(COUNTIF($C$1:$C$300,"P"),COUNTIF($D$1:$D$300,"D"))

again repeat click format in the middle block and choose pattern and choose your yellow color and then

click ok twice.

 

[mwvirk]

sorry for late reply. i am using office 2007 & 2010 but i am not able to use above conditional format. where should i go. pls help. thanks.

 

[Peter_SSs]

Try selecting B1, clear any existing Conditional Formatting then apply these:

Excel Workbook
	A	B	C	D
1	a	ABC
2	b		x
3	c		s
4	ABC		w	D
5	d		v
6	ABC		F	D
7	e		z
8
CF 2007+

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =COUNTIFS(A1:A300,B1,C1:C300,"F",D1:D300,"D")	Abc
B1	2. / Formula is =COUNTIFS(A1:A300,B1,C1:C300,"P",D1:D300,"D")	Abc

 

[mwvirk]

thanks peter. see if you can solve this as well:

cell A4 is having value D002 and I want to change background colour to RED if cell J in same row contain word "Full" and cell X in same row contains "Pending"
but if cell J is "Partial" and cell X is "Pending" then background colour for A4 should b YELLOW

same way if cell X is not "Pending" and if it is "Resolved" then text colour in A4 should be normal.

just to make it clear: I am checking the status of machine in one row. if it is fully down & issue is pending then it should highlight RED but if it is partially down then it should appear YELLOW

D002 low paper Partial Pending

but if it was reported Partial in some reason and ofcourse it is YELLOW but if in next row if it is reported again as fully down then it should change colour in RED in previous and current row.


D002 low ink Partial Pending
D002 paper jam Full Pending


i hope its clear !

Thanks.

 

[Peter_SSs]

See if this is what you mean.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
4	D002									Full														Pending
5	D002									Partial														Pending
6	D002									Full														Resolved
7	D002									Partial														Resolved
8	D002									Partial														Pending
9	D002									Full														Pending
CF mwvirk

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	1. / Formula is =OR(J4&X4="FullPending",J5&X5="FullPending")	Abc
A4	2. / Formula is =J4&X4="PartialPending"	Abc

 

[mwvirk]

i'm using office 2010 and applied above conditional formula but nothing is changed.

 

[Peter_SSs]

i'm using office 2010 and applied above conditional formula but nothing is changed.

My sample sheet above was also created in Excel 2010. So we need to determine what is different between my sheet and yours.

1. Does my layout appear correct?

2a. If so, does the colour appear correct for the dummy data that I have used?
(If not, tell me what cells in my sheet should be what colour.)

2b. If not, describe what I need to change to make the layout (or data) correct.

3. Try setting up a new worksheet with the same data and layout as mine and apply the CF to see if you can replicate my sheet. That may possibly provide some clues to the problem/solution.

If you want to post a small sample screen shot of your own sheet, my signature block contains three possible methods.