If this Cell Range contains True in each field, Highlight this different range in the same colour, No

[billsfree]

Hi.

New to Excel and was wondering if someone might be kind enough to lend me a hand

I have an Excel Worksheet with 8 columns:
The first range is D1:D4 (All Text Values)

The range adjacent is D5:D8 (All Boolean values)

Conditional Formatting formula: If(D5 AND D6 AND D7 AND D8) = True, Highlight the 8 fields in light green, otherwise do not do anything...

If((E2:H2) = "True", FORMAT(A2:D2) light green,""))

Sorry.. this is probably a stupid question but I have been working on this and trying to figure it out but just cannot get the range A2:D2 to also be light green if the range D1:D4 all equal true.
Another idea I had is maybe it is easier to refenece the row ? For example, if the 4 columns are true then highlight the row to light green, otherwise leave the row as it is...

Thanks for taking the time to read my Post and I hope you have an awesome Thursday !!!

Oblio

 

[etaf]

sorry i thought you wanted it blue
ok so
basically all cells are yellow apart the YES cells and if 4 yes's then its green

you could just format the whole area yellow to begin with
and then just use 2 rules for green

what else maybe type in the cells ?

so select all the cells A3 to H1000
and choose fill colour yellow
ALL cells are now yellow

then select

E3:H1000
and use the formula
=E3="yes"
in conditional formatting - now any cell with yes in will turn green

Select now
A3:H1000
and use formula
=COUNTIF($E3:$H3,"yes")=4
in conditional formatting - now if all 4 cells in E to H rows have a YES then the entire row will turn green

confrm-etaf-V4.xlsx
	A	B	C	D	E	F	G	H
1
2
3					yes	yes	yes	yes
4
5					yes	no	yes	yes
6							DVNY
7					yes	yes		DVNY
8					Yes	yes	DVNY	DVNY
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:H20	Expression	=E3="yes"	text	NO
A3:H1000	Expression	=COUNTIF($E3:$H3,"yes")=4	text	NO

confrm-etaf-V4.xlsx

Shared with Dropbox

www.dropbox.com

 

[billsfree]

Okay. I misunderstood the DVNY component. You most likely have faked your own death by now, and I completely understand if all I hear are crickets... honestly... I am so so sorry...

If both columns have DVNY in the range G3:H1000, Conditional Formatting should be Green Fill, White Font.

 

[etaf]

i'm sure

but this had to be in Excel for him

him, will come up with other things , as he does not seem 100% clear on the full requirements ........

You most likely have faked your own death by now, and I completely understand if all I hear are crickets... honestly.

happy to help, on this post if same sort of question with conditional formatting the range and adding formulas - thats ok
but i will probably be the only one that answers this - as there are now so many replies
However, under the rules , if its a completely new question , then a new thread needs to be opened


in the example
is it just row row 8 ?? that mets the condition , as an example

a few formulas you can use
i used
=COUNTIF($G3:$H3,"DVNY")=2
but you could use an AND($G3="DVNY", $H3="DVNY")

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
G3:H1000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIF($G3:$H3,"DVNY")=2

Format [Number, Font, Border, Fill] - format for a fill of green and a font of white
choose the format you would like to apply when the condition is true
OK >> OK

confrm-etaf-V4.xlsx
	A	B	C	D	E	F	G	H
1
2
3					yes	yes	yes	yes
4
5					yes	no	yes	yes
6							DVNY
7					yes	yes		DVNY
8					Yes	yes	DVNY	DVNY
9
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:H38	Expression	=COUNTIF($G3:$H3,"DVNY")=2	text	YES
E3:H20	Expression	=E3="yes"	text	NO
A3:H1000	Expression	=COUNTIF($E3:$H3,"yes")=4	text	NO

Dropbox

www.dropbox.com

 

[billsfree]

i'm sure

him, will come up with other things , as he does not seem 100% clear on the full requirements ........


happy to help, on this post if same sort of question with conditional formatting the range and adding formulas - thats ok
but i will probably be the only one that answers this - as there are now so many replies
However, under the rules , if its a completely new question , then a new thread needs to be opened


in the example
is it just row row 8 ?? that mets the condition , as an example

a few formulas you can use
i used
=COUNTIF($G3:$H3,"DVNY")=2
but you could use an AND($G3="DVNY", $H3="DVNY")

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
G3:H1000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIF($G3:$H3,"DVNY")=2

Format [Number, Font, Border, Fill] - format for a fill of green and a font of white
choose the format you would like to apply when the condition is true
OK >> OK

confrm-etaf-V4.xlsx
	A	B	C	D	E	F	G	H
1
2
3					yes	yes	yes	yes
4
5					yes	no	yes	yes
6							DVNY
7					yes	yes		DVNY
8					Yes	yes	DVNY	DVNY
9
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:H38	Expression	=COUNTIF($G3:$H3,"DVNY")=2	text	YES
E3:H20	Expression	=E3="yes"	text	NO
A3:H1000	Expression	=COUNTIF($E3:$H3,"yes")=4	text	NO

Dropbox

www.dropbox.com

Hi.

I am really not understanding the Formulas...

In the past, I have created a formula and just filled the other column or row to automatically have it calculated as Excel is super smart and knows what you are doing.

The last day or so, I have been trying to understand the Relative and Absolute cell references. So, in the past I have never used the $A$1 (Column A Row 1) and to be honest, I believe, You would use the Absolute reference if you were using a Constant so the formula would ALWAYS use the Absolute Value and a flowing Formula that adapts per rom and column ??? Still trying to get my head around this... could be that my brains are now liquified and have sneakily exited through my ears, groan...
In the example below, why isn't G3:H1000 and not G3:H38 ?
I also am wondering why the COUNTIF is COUNTIF($G3:$H3,"DVNY")=2 and =$J$7,$G$3:$H$38 is where it applies... $J$7 Column J row 7 ??? There is nothing there that I can see.

G3:H38

Expression

=COUNTIF($G3:$H3,"DVNY")=2

text

YES

It seems clear to me that the COUNTIF will search the row G3:H3 and return the count of 2, and based on that it will highlight appropriately...

If COUNTIF($G3:$H3,"DVNY")= 2 AND COUNTIF($G3:$H3,"YES")= 2, the whole row is green

Will keep at it... Just wish I had time to take a course in Excel... I am only self taught and rarely use Excel...

 

[etaf]

In the example below, why isn't G3:H1000 and not G3:H38 ?
I also am wondering why the COUNTIF is COUNTIF($G3:$H3,"DVNY")=2 and =$J$7,$G$3:$H$38 is where it applies... $J$7 Column J row 7 ??? There is nothing there that I can see.

My BAD
J should not be selected - I'll check

I did not use to row 1000
now updated

confrm-etaf-V5.xlsx
	A	B	C	D	E	F	G	H
1
2
3					yes	yes	yes	yes
4
5					yes	no	yes	yes
6							DVNY
7					yes	yes		DVNY
8					Yes	yes	DVNY	DVNY
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:H1000	Expression	=COUNTIF($G3:$H3,"DVNY")=2	text	YES
E3:H1000	Expression	=E3="yes"	text	NO
A3:H1000	Expression	=COUNTIF($E3:$H3,"yes")=4	text	NO

you are right on some of the absolute/relative

$A1 - fixed the column to use A only as you drag across, but as you go down the row changes
A$1 - fixes the row to 1 so as you copy down - they row is fixed at row 1 , but as you drag across , the column will change
$A$1 - fixes in cell A1 regardless of where you copy - all have there place

Now conditional formatting - checks each cell and applies the formula - hence why if you select row 3 - the fomula must have row 3 included

selecting G3 to H1000
conditional formating will look at cell G3 and use the formula

COUNTIF($G3:$H3,"DVNY")= 2

as G and H are fixed it only looks in those 2 cells
then it moves to H3 - if the ciolumns had not been fixed - it would look in H & J - But as the columns are fixed then in H it still looks at G and H
then it moves down to row 4
and because the row is not fixed - ith can do
COUNTIF($G4:$H4,"DVNY")= 2

etc - does that help

 

[billsfree]

Dang it, Wayne ! Are you married ?!!!
I should honestly be paying you tuition... THANK YOU SOOOO MUCH for sticking this out with me...

Will check this out after lunch... feeling much better that j was not supposed to be there !!!

I really am going to owe you lunch at the very least, sir !!!!!!! Cannot believe how amazing you all are helping people for free and I just want to thank you for really really helping me... one day I hope I can help someone, too :D

Will let you know how it goes !

Cheers !

 

[etaf]

you are welcome, what country are you in - lunch 16:45 - , i guess a different time zone , i'm in UK

 

[billsfree]

you are welcome, what country are you in - lunch 16:45 - , i guess a different time zone , i'm in UK

Yes ! Ontario, Canada.

How about you ?

Also, if E3 and F3 = Yes AND G3 and GH="DVNY" highlight the entire row (just like 4 yes counts). DVNY can only occur in the G and H rows...

COUNTIF($G3:$H3,"DVNY")= 2 AND E3:F3="Yes" ???

 

[etaf]

Also, if E3 and F3 = Yes AND G3 and GH="DVNY"

GH ?

not sure what you are after

maybe
select A3:H1000
=AND($E3="Yes",$F3="Yes", $G3=,"DVNY", $H3,="DVNY")
format fill green
but
we have a white font for 2 dvny
do you want white for yes?

 

[billsfree]

GH ?

not sure what you are after

maybe
select A3:H1000
=AND($E3="Yes",$F3="Yes", $G3=,"DVNY", $H3,="DVNY")
format fill green
but
we have a white font for 2 dvny
do you want white for yes?

You are right.. it would be easier to just have black text and green fill... ok, I thought it might be possible to use AND somewhere !